Hi Guys
need help with the following formulas in Excel 2007. I have spent hours on this including web-searches.
I have data recorded during different times of the day. I need a min/ave/max for every day.
I discovered AVERAGEIFS which works fantastically well, but doesn't for min/max. Here I discovered array formulas, but 2 irritating things happen.
1. Even if there is no data (blank cells) the min/max formulas return zero "0". I want this to be blank.
2. The min formula will not return a minimum value unless ALL the cells have values for a particular date (I assume it sees blank cells as zeros). See 03/10/2013 as an example.
The formulas I've use are as follows:
Cell E3 - Min:
=+MIN(IF($A$3:$A$26=D4,$B$3:$B$26))
Cell F3 - Average:
=IF(ISERROR(AVERAGEIFS($B$3:$B$26,$A$3:$A$26, D4)),"-",AVERAGEIFS($B$3:$B$26,$A$3:$A$26, D4))
Cell G3 - Max:
=+MAX(IF($A$3:$A$26=D4,$B$3:$B$26))
Will appreciate your help.
Thank you
Greeting from Tanzania
Don't know why the image is not showing but it's located here:
http://www.flickr.com/photos/jhemurray/10207355463/
need help with the following formulas in Excel 2007. I have spent hours on this including web-searches.
I have data recorded during different times of the day. I need a min/ave/max for every day.
I discovered AVERAGEIFS which works fantastically well, but doesn't for min/max. Here I discovered array formulas, but 2 irritating things happen.
1. Even if there is no data (blank cells) the min/max formulas return zero "0". I want this to be blank.

2. The min formula will not return a minimum value unless ALL the cells have values for a particular date (I assume it sees blank cells as zeros). See 03/10/2013 as an example.

The formulas I've use are as follows:
Cell E3 - Min:
=+MIN(IF($A$3:$A$26=D4,$B$3:$B$26))
Cell F3 - Average:
=IF(ISERROR(AVERAGEIFS($B$3:$B$26,$A$3:$A$26, D4)),"-",AVERAGEIFS($B$3:$B$26,$A$3:$A$26, D4))
Cell G3 - Max:
=+MAX(IF($A$3:$A$26=D4,$B$3:$B$26))
Will appreciate your help.
Thank you
Greeting from Tanzania
Don't know why the image is not showing but it's located here:
http://www.flickr.com/photos/jhemurray/10207355463/
Last edited: