MAX/MIN with SUMPRODUCT
 

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

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(D8:D73="H",IF(ISNUMBER(F8:F73),F8:F73)))

Biff

wrote in message
oups.com...
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

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)))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
oups.com...
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" wrote:
....
=MIN(IF(D8:D73="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))))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

Biff

"Max" wrote in message
...
"Biff" wrote:
...
=MIN(IF(D8:D73="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))))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

Ah, but usually the child in me wins through the adult ! <g
Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

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 ..)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
oups.com...
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$7
3))))}

Works a treat.
Thanks
Neil