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

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

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
news:(E-Mail Removed)...
> 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
>
>

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
news:(E-Mail Removed)...
>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
>
>

Many Thanks to all of you.

Great Info!


"Charles Williams" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>>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
>>
>>
>
>

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