Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying/speeding up this formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying/speeding up this formula
Use 2 cells The first for the sumproduct the second for the if
-- Gary''s Student - gsnu200737 "Keith R" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying/speeding up this formula
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! "Gary''s Student" wrote in message ... Use 2 cells The first for the sumproduct the second for the if -- Gary''s Student - gsnu200737 "Keith R" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying/speeding up this formula
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). "Keith R" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying/speeding up this formula
"JMB" wrote...
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)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying/speeding up this formula
Yep, I certainly overlooked that!
"Harlan Grove" wrote: "JMB" wrote... 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplifying a formula | Excel Worksheet Functions | |||
Simplifying Formula (part 1) | Excel Discussion (Misc queries) | |||
Simplifying Formula (Part 2) | Excel Discussion (Misc queries) | |||
Simplifying formula | Excel Discussion (Misc queries) | |||
Simplifying array formula which evaluates as error. | Excel Worksheet Functions |