Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Average Function with Day of Week Criteria
This is for Excel 2000.
I want to compute averages based on the day of the week listed in another column but within the same row. I already have the day of the week figured out, column A has numbers 1-7, 1=Sunday, 2=Monday, etc. What I want is to take the AVERAGE of B1:B200, *if* the value in column A=1, or whatever number or numbers I specify. |
#2
|
|||
|
|||
Average Function with Day of Week Criteria
One way ..
Put in C1: =ROW() Put in D1, and array-enter (press CTRL+SHIFT+ENTER): =AVERAGE(IF(($B$1:$B$200<"")*($A$1:$A$200=C1),$B$ 1:$B$200)) Select C1:D1, copy down to D7 D1 to D7 will return the results for the corresponding day of week listed in C1:C7 (You'd get the full list for days 1 - 7) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cheese" wrote in message ... This is for Excel 2000. I want to compute averages based on the day of the week listed in another column but within the same row. I already have the day of the week figured out, column A has numbers 1-7, 1=Sunday, 2=Monday, etc. What I want is to take the AVERAGE of B1:B200, *if* the value in column A=1, or whatever number or numbers I specify. |
#3
|
|||
|
|||
Average Function with Day of Week Criteria
... and a quick sample, in case needed:
http://cjoint.com/?lck01cr7y4 Cheese_newusers.xls Note that the average formula as suggested will ignore empty cells or formula cells within B1:B200 evaluating to null (""), re - the condition: .... ($B$1:$B$200<"") .. If we need it to ignore cells containing zeros as well, then we could add-on say, the condition: .. ($B$1:$B$2000) .. i.e. put instead in D1, array-enter as before, and copy down to D7: =AVERAGE(IF(($B$1:$B$2000)*($B$1:$B$200<"")*($A$ 1:$A$200=C1),$B$1:$B$200)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#4
|
|||
|
|||
Average Function with Day of Week Criteria
Max,
For the OPs information really, but it may not be appropriate to do the blank test. If a week number is 1 and the value is blank, it may be correct to include that in the average =AVERAGE(IF($A$1:$A$200=C1,$B$1:$B$200)) BTW, brought a smile to my face seeing you use cjoint :-)) Regards Bob "Max" wrote in message ... .. and a quick sample, in case needed: http://cjoint.com/?lck01cr7y4 Cheese_newusers.xls Note that the average formula as suggested will ignore empty cells or formula cells within B1:B200 evaluating to null (""), re - the condition: ... ($B$1:$B$200<"") .. If we need it to ignore cells containing zeros as well, then we could add-on say, the condition: .. ($B$1:$B$2000) .. i.e. put instead in D1, array-enter as before, and copy down to D7: =AVERAGE(IF(($B$1:$B$2000)*($B$1:$B$200<"")*($A$ 1:$A$200=C1),$B$1:$B$200)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
|
|||
|
|||
Average Function with Day of Week Criteria
Thanks for the comments, Bob. Think the options given would enable the OP to
figure out the conditional checks required, and how to add/remove conditions as may be required. BTW, brought a smile to my face seeing you use cjoint :-)) Ay, it was only the other day that I shared with Roger G the steps on using cjoint <g. But I guess savefile.com still remains my primary choice, due to its other features (project/folder feature for instance). -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
|
|||
|
|||
Average Function with Day of Week Criteria
"Max" wrote in message
... Ay, it was only the other day that I shared with Roger G the steps on using cjoint <g. But I guess savefile.com still remains my primary choice, due to its other features (project/folder feature for instance). Yes, savefile does seem to be more permanent, but for simple transient files, I like the cjoint look and feel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average function and two criteria | Excel Worksheet Functions | |||
Average of numbers within a range meeting certain criteria | Excel Worksheet Functions | |||
Using ADD function within DCOUNT criteria | Excel Discussion (Misc queries) | |||
Date & Time | New Users to Excel | |||
Using the average function | Excel Worksheet Functions |