Use with Excel

[Frank]

If I need to do something like the following in Excel, would I use a VB
macro?

What I have is a number of occurrences with the same number (top 10
rows), followed by some value. I want a unique row per a column 1 value
with the average of all those after it. I can do this manually but
really want to have this done automatically once I toss in some numbers.

Thanks,
Frank


1 53
1 60
1 60
1 60
1 60
2 60
2 60
2 60
2 60
2 58




1 58.6
2 59.6

[Steve Gerrard]

Re: Use with Excel


"Frank" <fgeck@optonlin e.net> wrote in message
news:scysb.1124 6$hn3.3784631@n ews4.srv.hcvlny .cv.net...[color=blue]
> If I need to do something like the following in Excel, would I use a[/color]
VB[color=blue]
> macro?
>
> What I have is a number of occurrences with the same number (top 10
> rows), followed by some value. I want a unique row per a column 1[/color]
value[color=blue]
> with the average of all those after it. I can do this manually but
> really want to have this done automatically once I toss in some[/color]
numbers.[color=blue]
>
> Thanks,
> Frank
>
>
> 1 53
> 1 60
> 1 60
> 1 60
> 1 60
> 2 60
> 2 60
> 2 60
> 2 60
> 2 58
>
>
>
>
> 1 58.6
> 2 59.6
>
>[/color]
If you know the range of the column one values, say 1 - 9, you could set
up a row for each one.
Assume your data is in A1:B10, then enter this in A12 and B12, and so
on:
1 =SUMIF(A1:A10,A 12,B1:B10)/COUNTIF(A1:A10, A12)
2 =SUMIF(A1:A10,A 13,B1:B10)/COUNTIF(A1:A10, A13)
etc.

I don't think there is an AverageIf function, but SumIf divided by
CountIf should do it. (To be complete, it should have a check for Div by
0).
Translated to English, the formula in B12 above says: Check the values
in A1:A10. For those equal to A12, sum the corresponding values in
B1:B10. Then Count the values in A1:A10 that are equal to A12, and
divide by the count.

If you don't know the range of column one, then you would need a macro,
so you can get a variable number of average rows.