Average Percent By Day

[secretcodepastedhere]

Okay guys, should be an easy one for you but it's giving me a headache (probably lack of sleep helping there too).

A is Dates, B is a starting #, C is an ending #, D is the difference, and E is the growth from B to C as a Percentage.

I need to set up a Average by day of week, ie: Monday: (average percentage of all "monday's" listed)

I tried:

=AVERAGE(IF(ISNUMBER(E$3:E$33),IF(G$3:G$33=L3,IF(E$3:E$33<>0,E$3:E$33))))

where G was weekday, and L3 was "Monday", but it just returns the average of the entire E column.

Thoughts?

 

[barry houdini]

I don't think you need the ISNUMBER part, this should be sufficient

=AVERAGE(IF(G$3:G$33=L3,IF(E$3:E$33<>0,E$3:E$33)))

...but either way the formula is an "array formula" which means you need to enter it in a special way - paste formula in cell - use F2 key to select then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar.

If you are using Excel 2007 or later, though, you can use AVERAGEIFS function which can be entered normally, i.e.

=AVERAGEIFS(E$3:E$33,G$3:G$33,L3,E$3:E$33,"<>0")

 

[secretcodepastedhere]

That does it, thank you very much, I was thinking it was going to have to use match somehow, but this is so simple!