MAX/MIN with SUMPRODUCT

[neil40]

Hi folks

I am using the following:
=IF($F$8="",0,SUMPRODUCT(MAX(($D$8:$D$73="H")*($F$8:$F$73))))
to show the highest attendance in a seasons fixture list.
Thus this changes as each game is completed and I fill the attendance.
The IF part is just to put in a 0 if the season hasn't started.
However, if I use that same formula with MIN,
=IF($F$8="",0,SUMPRODUCT(MIN(($D$8:$D$73="H")*($F$8:$F$73))))
I just get a 0
Is this because it's reading blank cells as zero's?
If so, how do i overcome this to just find the lowest value among
values entered (ignore blank cells)

Many thanks
Neil

 

[Biff]

Hi!

Is this because it's reading blank cells as zero's?

Yes

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(D873="H",IF(ISNUMBER(F8:F73),F8:F73)))

Biff

 

[Max]

Perhaps an alternative to try, array-entered
(i.e. press CTRL+SHIFT+ENTER):

=IF($F$8="",0,MIN(IF(($D$8:$D$73="H")*($F$8:$F$73<>""),$F$8:$F$73)))

 

[Max]

:
....

=MIN(IF(D873="H",IF(ISNUMBER(F8:F73),F8:F73)))

To align with what the OP posted, suggest just a slight tweak to the above
array formula <g>:

=IF($F$8="",0,MIN(IF($D$8:$D$73="H",IF(ISNUMBER($F$8:$F$73),$F$8:$F$73))))

 

[Biff]

Max, you have to let them do "some" of the work!

Biff

 

[Max]

Max, you have to let them do "some" of the work!

Ah, but usually the child in me wins through the adult ! <g>
Cheers.

 

[neil40]

Thanks folks.

I went away and took another look in the interim, and concluded I'd
missed the array bit (even though I didn't use CTRL SHIFT ENTER on the
MAX calc!!) and ended up with:
{=IF($F$8="",0,SUMPRODUCT(MIN(IF(($D$8:$D$73="H")*($F$8:$F$73<>""),$F$8:$F$73))))}

Works a treat.
Thanks
Neil

 

[Max]

Good to hear that you managed to work it out, Neil ..
but I'm not sure if the SUMPRODUCT(..) bit was required in this instance
(might be superfluous here, could be dispensed with ..)