AVERAGEIFS Equivalent for MIN and Max

[murjey]

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/

 

[TheCman81]

The max formula you have above is fine to use as it will stil return the max value weather there's a blank or not

For the MIN you can use the following array formula

=MIN(IF($A$3:$A$10=D4,IF($B$3:$B$10<>"",$B$3:$B$10)))

 

[XOR LX]

Perhaps:

MIN (cell E4 - presume you typoed "E3" in your post, as that formula is referencing D4?):

=IFERROR(1/(1/(MIN(IF($A$3:$A$26=D4,IF($B$3:$B$26<>"",$B$3:$B$26))))),"")

MAX (cell F4):

=IFERROR(1/(1/(MAX(IF($A$3:$A$26=D4,$B$3:$B$26)))),"")

 

[circledchicken]

Hi,

Another alternative is to create a PivotTable from your raw data table.
Once created:

-> Add the Date field to the Row Labels area
-> Add the Value field to the Values area (drag it in three separate times)
-> Change Value field summarization measures to Min, Average and Max

The PivotTable measures will automatically ignore blanks in the raw data.
The output might look something like this:

Excel 2013
	D	E	F	G
1
2	Row Labels	Min of Value	Average of Value2	Max of Value3
3	01/10/2013	335	539	875
4	02/10/2013
5	03/10/2013	275	325	375
6	04/10/2013	290	386	470
Sheet1

 

[murjey]

Hi Guys

TheCman81 = thank you, easy solution to second question!
XOR LX = fantastic - solution to both problems!
circledchicken = the dark arts of PivotTables Sounds simpler and I'll give it a go over the weekend. Thank you

Cheers
John