Want to average values grouped by value in a certian column (sql example included)

[LordJezo]

It can be done with SQL using:

SELECT Day, AVG(Users) Avg Users FROM NOTESMON GROUP BY Day

But that is not very portable

On the attached sheet the statement needs to take every value from th
User column (J) where the Day column (H) was the same, add them up, an
take the average of them.

So if Day was 30 for the first 10 rows it would take the value in th
users columns for those 10 rows, get their average, and plop it ou
next to the vaule of the day, so it would be 30 and then next to tha
the average value of all the entries of User that correspond to 30

Advice

Attachment filename: ntmn0404_querytest.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54238

 

[AlfD]

Hi!

=SUM((A1:A18)*((B1:B18)="a"))/COUNTIF((B1:B18),"a")
array-entered (Ctrl+SHift+Enter)

will average all values in col A1:A18 where col B contains "a" .

Is this the sort of thing you need?


Al

 

[LordJezo]

That is close, but is there any way for the value of "a" to b
automatically retrieved from the Day column so I get the value
automatically with out needing to put change the formula for 3
different values of "a"?

With the SQL statements it would just fill down for say, 30 values o
"a" (1 to 30) and put in the averages automatically

 

[AlfD]

Hi!

From what you say, I think Data>Subtotals might be a better option.

Al

 

[LordJezo]

That worked almost perfectly!!

Is there anyway to get Excel to put the data in a separate colum
instead of putting the subtotals under each grouping for easie
viewing, so you don’t need to scroll all the way down the page to se
each of the subtotals

 

[AlfD]

Hi!

Glad it's working.

Things could be even better: do you see little buttons (1,2,3) at th
top left when in the subtotal view? Try them: 2 will probably show yo
what you want: just the subtotal rows.

As for copying them: highlight the block of subtotals: F5> Specia
select Visible Cells only: copy: paste it where you will.

Al