Simplifying/speeding up this formula

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

Is there a way to change the following formula from
=if(calculation result >0, calculate it again, "") to =if(calculation result
0, use that result, "") ?
Click to expand...

=IF(SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C5)*1,('Raw
Data'!$B$2:$B$30000=I$1)*1)>0,SUMPRODUCT(('Raw
Data'!$A$2:$A$30000=$C5)*1,('Raw Data'!$B$2:$B$30000=I$1)*1),"")

Just thinking if there is a clever way to do this, it would cut worksheet
calculation time in half...but I'm not sure how Excel calculates and stores
this data, so my assuption may be incorrect anyway...

Thanks,
Keith
 
Doh! (slaps forehead).

Interestingly, I made other minor modifications to the sheet, and I'm not
sure what I did, but the whole thing is recalculating an order of magnitude
slower than before (independent of the 2-cell solution below). Looks like I
have some more troubleshooting to do :(

Thanks!
 
Or you could eliminate the IF statement completely
=SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C5)*1,('Raw Data'!$B$2:$B$30000=I$1)*1)

and use a custom number format (Format/Cells/Custom) of 0;; or #,###;; to
display a blank for zero values (and negative values, but you should not get
any negatives w/this formula).
 
JMB said:
Or you could eliminate the IF statement completely
=SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C5)*1,
('Raw Data'!$B$2:$B$30000=I$1)*1)
Click to expand...
....

Or eliminate a few unnecessary characters,

=SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C5)*('Raw Data'!$B$2:$B$30000=I$1))
 
Yep, I certainly overlooked that!

Harlan Grove said:
....

Or eliminate a few unnecessary characters,

=SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C5)*('Raw Data'!$B$2:$B$30000=I$1))
Click to expand...
 
Back
Top