Sumproduct and Max

[xl_psychic]

All:

I am making use of the following formula in order to get the maximum value within a range.

=SUMPRODUCT(MAX(($B$9:$B$1110=$B$7)*(L$9:L$1110<>"")*(L$9:L$1110)))

But the formula is giving me a #VALUE output.

Could you please let me know where am I going wrong with this.

Help Appreciated...

 

[fairwinds]

Hi,

Propably because you have "formula blanks"

Try:

=MAX(IF($B$9:$B$1110=$B$7,L$9:L$1110))

Confirmed with Ctrl + shift + enter.

 

[xl_psychic]

Fairwinds:

Could you explain me the "formula blanks" concept.. I didn't quite get it..

I ended up using the MAX(IF... Array formula..

 

[fairwinds]

Fairwinds:

Could you explain me the "formula blanks" concept.. I didn't quite get it..

I ended up using the MAX(IF... Array formula..

I mean cell holding e.g.

=""

 

[xl_psychic]

Fairwinds:

The formula wouldn't work even if the "(L$9:L$1110<>"")" part was removed from the same..

I added this part after the initial formula =SUMPRODUCT(MAX(($B$9:$B$1110=$B$7)*(L$9:L$1110))) too gave me a #VALUE error..

Any reasons for the same..

 

[fairwinds]

Yes.

...)*(L$9:L$1110)))


If you try to multiplicate a formula blank you get an error.

 

[Aladin Akyurek]

Fairwinds:

The formula wouldn't work even if the "(L$9:L$1110<>"")" part was removed from the same..

I added this part after the initial formula =SUMPRODUCT(MAX(($B$9:$B$1110=$B$7)*(L$9:L$1110))) too gave me a #VALUE error..

Any reasons for the same..

=SUMPRODUCT(MAX(--($B$9:$B$1110=$B$7),L$9:L$1110))

would succeed but would be undesirable call to SumProduct. Some people do so at MS worksheet.functions. Looks like they want to generalize their beloved function and/or to avoid confirming a formula with control+shift+enter. MAX can also grind computed arrays if Excel is properly instructed with the control+shift+enter sequence (see Fairwinds's suggestion).

 

[fairwinds]

=SUMPRODUCT(MAX(--($B$9:$B$1110=$B$7),L$9:L$1110))

Have the students been tough today?

 

[Aladin Akyurek]

=SUMPRODUCT(MAX(--($B$9:$B$1110=$B$7),L$9:L$1110))

Have the students been tough today?

The 2nd years are busy writing an extended report covering a collection of models they had to set up last two weeks... I guess they don't have time for that.

 

[fairwinds]

Great.

That should leave the teacher plenty of time to put together a SUMPRODUCT formula that actually takes the condition into consideration.