Max/min functions similar to SUMIF

[Monte Milanuk]

Hello,

I am trying to set up a standard test form for some data. The way the
data will normally be presented/entered will be like a frequency
distribution as such:

172.60 50
172.70 63
172.80 26
172.90 0
173.00 11
173.10 0

I want to have a block that presents the info in a nutshell - min, max,
range, mode, average, stdev of the above array. So far I have the
average and stdev part working (thanks Wigi!), and the range is simple
max minus min... what I am trying to figure out is how to calculate the
max and min. If the first and last values in column A were non-zero,
it'd be simple. As it is... I'm trying to figure out how to implement
max() when the last couple listed entries may have zero count. It looks
like sumif works about like what I want - i.e. count a cell value only
if it meets a certain parameter. I would think that some form of if()
statement would be in order, but I'll be darned if I could make it work
the first couple go-arounds.

Any help or advice would be greatly welcomed.

Thanks,

Monte

 

[Pete_UK]

I'm not sure how MAX would be affected by having zeros or blanks in
some of the cells, so I presume you mean MIN. You can try this array*
formula:

=MIN(IF(cell_range<0,cell_range,10E10))

where cell_range might be something like A2:A7. The 10E10 is just a
very large number, so it is unlikely to figure in the MIN calculation.

* An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter>. If you do this correctly then Excel
will wrap curly braces { } around the formula when viewed in the
formula bar - do not type these yourself. If you need to edit/amend
the formula then you should use CSE again.

Hope this helps.

Pete

 

[Monte Milanuk]

I'm not sure how MAX would be affected by having zeros or blanks in
some of the cells,

Please look at the sample data I provided... it should be relatively
clear what I'm working with. I could custom tune the range for every
max formula to preclude any time that the highest weight has zero count,
but I'm trying to get away from having to tweak it every time.

Thanks,

Monte

 

[Pete_UK]

With your data in A1:B6, I think you want:

=MAX(IF(B1:B6>0,A1:A6))

and:

=MIN(IF(B1:B6>0,A1:A6,10E10))

both array formulae, so commit with CSE as previously advised.

Hope this helps.

Pete