Average price depending on the day of the week

[Patrick69]

Hi All,
Long time reader but first time poster...
I have been searching everywhere for a simple solution to get the average fuel cost depending on which day of the week it is. In other word, how much does fuel cost on Mondays, Tuesdays, etc.
This is my unsuccessful attempt:
=SUMIF(A1:A100,"Monday", B1:B100)/COUNTIF(A1:A100,"Monday")
Where column A has dates and B has the prices I paid per gallon on that day.
I also tried using a 3rd column with just the day of the week (using =A1 and formating to return the day only) but no luck. #DIV/0! Using just SUMIF doesn't work either.
My problem I think is the day...
I am using Excel 2003.
Many thanks for all the help you have already given me,
Patrick

 

[Peter_SSs]

So following that principal, I assume (will verify as you should never...) that =AVERAGE(IF(WEEKDAY(A1:A100)=2,B1:B100)) would also work as is?

When you (try to) verify this you will find, as you did before, that it will fail if any of the cells in the column A range contain other text entries like "???"

(Intuitively for me, 1 is Monday).

If you look up the WEEKDAY function in the built-in Help you will find that this function has an optional second argument where you can influence what is regarded as the first day of the week. So, if you didn't have any 'problem' text entries in column A you could have used

=AVERAGE(IF(WEEKDAY(A1:A100,2)=1,B1:B100))
Where the blue 2 indicates the week starts on a Monday and so the red 1 represents Mondays.

Should I be able to see the {}? And if yes, where? Although all is working I do not see them?

Yes, if you click on the cell containing the array formula and look in the Formula Bar, you should see the {} around the formula.

 

[mkruzel88]

Peter,

How do I do this across multiple sheets. I'm trying to average values for each day of the week for an entire year but each month is a separate sheet. I've tried =AVERAGE(IF(WEEKDAY(Sheet1:Sheet12!A1:A100)=2,Sheet1:Sheet12!B1:B100))

Based on your formula

=AVERAGE(IF(WEEKDAY(A1:A100)=2,B1:B100))

The original formula works great for each sheet but I can get it work across multiple.

Thanks in advance for any help


Mike