Simplifying/speeding up this formula

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, "") ?

=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
 
K

Keith R

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!
 
J

JMB

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).
 
H

Harlan Grove

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)
....

Or eliminate a few unnecessary characters,

=SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C5)*('Raw Data'!$B$2:$B$30000=I$1))
 
J

JMB

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top