Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does Excel evaluate embedded "IF" statements
I am working on a large worksheet (50 meg, 13,000 rows) not of my making.
It has a huge number of embedded IF statements with VLOOKUPS also embedded in them. Each file is set to calculate manually after large segments of data are pulled out and replaced. the calculation can take over an hour. Question: How does excel evaluate embedded IFs. If the first value is true, does Excel take that value and stop or does it evaluate all the IFs in the formula regardless of whether the first one is true? I would say that in Formula "A" the likelyhood of the first IF condition evaluating to true is less than thirty percent. The likelyhood of the second IF condition evaluating to true is less than twenty percent. The likelyhood of the third IF condition evaluating to true is more than sixty percent. Would rearranging the order of the formula components make calculation faster. Thx for any help/ideas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does Excel evaluate embedded "IF" statements
If TRUE, then only the first argument gets evaluated.
If FALSE, then the first argument gets skipped and the second argument gets evaluated. -- Gary's Student "Kc-Mass" wrote: I am working on a large worksheet (50 meg, 13,000 rows) not of my making. It has a huge number of embedded IF statements with VLOOKUPS also embedded in them. Each file is set to calculate manually after large segments of data are pulled out and replaced. the calculation can take over an hour. Question: How does excel evaluate embedded IFs. If the first value is true, does Excel take that value and stop or does it evaluate all the IFs in the formula regardless of whether the first one is true? I would say that in Formula "A" the likelyhood of the first IF condition evaluating to true is less than thirty percent. The likelyhood of the second IF condition evaluating to true is less than twenty percent. The likelyhood of the third IF condition evaluating to true is more than sixty percent. Would rearranging the order of the formula components make calculation faster. Thx for any help/ideas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does Excel evaluate embedded "IF" statements
All your questions are answered here together with a lot more info you may
find useful http://www.decisionmodels.com/ Regards, Peter T "Kc-Mass" <connearney_AT_comcast_PERIOD_net wrote in message . .. I am working on a large worksheet (50 meg, 13,000 rows) not of my making. It has a huge number of embedded IF statements with VLOOKUPS also embedded in them. Each file is set to calculate manually after large segments of data are pulled out and replaced. the calculation can take over an hour. Question: How does excel evaluate embedded IFs. If the first value is true, does Excel take that value and stop or does it evaluate all the IFs in the formula regardless of whether the first one is true? I would say that in Formula "A" the likelyhood of the first IF condition evaluating to true is less than thirty percent. The likelyhood of the second IF condition evaluating to true is less than twenty percent. The likelyhood of the third IF condition evaluating to true is more than sixty percent. Would rearranging the order of the formula components make calculation faster. Thx for any help/ideas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does Excel evaluate embedded "IF" statements
Embedded IFs are evaluated left-to-right, outermost ( ) to innermost ()
Evaluation does not continue after the first TRUE. for suggestions on how to speed-up lookups see http://www.decisionmodels.com/optspeede.htm regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Kc-Mass" <connearney_AT_comcast_PERIOD_net wrote in message . .. I am working on a large worksheet (50 meg, 13,000 rows) not of my making. It has a huge number of embedded IF statements with VLOOKUPS also embedded in them. Each file is set to calculate manually after large segments of data are pulled out and replaced. the calculation can take over an hour. Question: How does excel evaluate embedded IFs. If the first value is true, does Excel take that value and stop or does it evaluate all the IFs in the formula regardless of whether the first one is true? I would say that in Formula "A" the likelyhood of the first IF condition evaluating to true is less than thirty percent. The likelyhood of the second IF condition evaluating to true is less than twenty percent. The likelyhood of the third IF condition evaluating to true is more than sixty percent. Would rearranging the order of the formula components make calculation faster. Thx for any help/ideas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does Excel evaluate embedded "IF" statements
Many Thanks to all of you.
Great Info! "Charles Williams" wrote in message ... Embedded IFs are evaluated left-to-right, outermost ( ) to innermost () Evaluation does not continue after the first TRUE. for suggestions on how to speed-up lookups see http://www.decisionmodels.com/optspeede.htm regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Kc-Mass" <connearney_AT_comcast_PERIOD_net wrote in message . .. I am working on a large worksheet (50 meg, 13,000 rows) not of my making. It has a huge number of embedded IF statements with VLOOKUPS also embedded in them. Each file is set to calculate manually after large segments of data are pulled out and replaced. the calculation can take over an hour. Question: How does excel evaluate embedded IFs. If the first value is true, does Excel take that value and stop or does it evaluate all the IFs in the formula regardless of whether the first one is true? I would say that in Formula "A" the likelyhood of the first IF condition evaluating to true is less than thirty percent. The likelyhood of the second IF condition evaluating to true is less than twenty percent. The likelyhood of the third IF condition evaluating to true is more than sixty percent. Would rearranging the order of the formula components make calculation faster. Thx for any help/ideas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does Excel evaluate embedded "IF" statements
I wish VBA were like that.
As a worksheet function =IF(TRUE,1,1/0) returns an unproblematic 1 but the VBA equivalent IIF(True,1,1/0) throws a divison by zero error. This renders the IIF() function much less useful than it could be, which might be one of the reasons that VBA programmers seem to not use it much. Gary''s Student wrote: If TRUE, then only the first argument gets evaluated. If FALSE, then the first argument gets skipped and the second argument gets evaluated. -- Gary's Student |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can the Excel cell fill color be changed using "IF" statements? | Excel Worksheet Functions | |||
Evaluate "nested-merge" function? | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Need more than 7 nested "IF" statements in Excel" | Excel Discussion (Misc queries) | |||
"Activeworkbook" function not valid when workbook embedded in Lotus Notes? | Excel Programming |