Shortening the Formula

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

This formula works:

=SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w"))

I tried a lot of different ways, but I couldn't find a way to shorten
this part:

(E3:E1000=6)+(E3:E1000=6.5)

I'm sure there is a way to make that more efficient.

Thanks
 
You do not really need the double negation. It is used to transform
TRUE/FALSE to 1/0 - Excel does this whenever a math operation is performed
on a Boolean value. But you are multiplying so the double neg is redundant.
either =SUMPRODUCT(((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w"))
or =SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5)),--(M3:M1000="w"))

Of course, the following also works
SUMPRODUCT(--(E3:E1000=6),--(M3:M1000="w"))+SUMPRODUCT--(E3:E1000=6.5),--(M3:M1000="w"))

Do you notice that the formula slows down recal? Is that why you want to
change it?
best wishes
 
Jim,

A lot depends on what your values are - if you only have integers and
integer + .5 values, you could use

=SUMPRODUCT((INT(E3:E1000)=6)*(M3:M1000="w"))

HTH,
Bernie
MS Excel MVP
 
=SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w"))
I tried a lot of different ways, but I couldn't find
a way to shorten this part:
(E3:E1000=6)+(E3:E1000=6.5)
I'm sure there is a way to make that more efficient.
Click to expand...

"Shortness" doesn't always equate to efficiency.

This long formula is very efficient compared to the original:

=SUMPRODUCT(--(ISNUMBER(MATCH(E3:E1000,{6,6.5},0))),--(M3:M1000="w"))

Average calculation times (5 calculations):

Short formula: 0.002508 secs
Long formula: 0.001748 secs
 
A Biff has shown you, shorter does not always mean more efficient. Here is a
formula that is shorter than your originally posted one but which I think is
less efficient...

=SUMPRODUCT((ABS(E3:E1000-6.25)=0.25)*(M3:M1000="w"))
 
Back
Top