reducing the number of times an IF statement needs to be calculated in embedded IF statements
 

Hi,

I have a long formula that is getting too long. It has 3 embedded IF
statements, with the logical test below having to be calculated each time.
(Planning!X14/365.25)+1900-INT((Planning!X14/365.25)+1900)

For example, when the logical test solves this to 2004, is there some way to
store that "2004" in memory the first time and recall it as "2004" in the
formula for the other two logical tests. Otherwise the formula is getting so
long, and these three embedded IF are just a portion of a longer formula.

In summary, is this possible to, in the middle of a formula, store something
in memory and recall it from memory for use in other places in the formula?

I think you'll say no, that's what VBA is for. That's ok, I just would like
to know for sure.

Thanks,

Harold

reducing the number of times an IF statement needs to be calculated in embedded IF statements
 

Hi Harold,

Use a separate cell for the intermediate result. Much easier testing too!

--
Kind regards,

Niek Otten

"Harold Good" wrote in message
...
Hi,

I have a long formula that is getting too long. It has 3 embedded IF
statements, with the logical test below having to be calculated each time.
(Planning!X14/365.25)+1900-INT((Planning!X14/365.25)+1900)

For example, when the logical test solves this to 2004, is there some way
to store that "2004" in memory the first time and recall it as "2004" in
the formula for the other two logical tests. Otherwise the formula is
getting so long, and these three embedded IF are just a portion of a
longer formula.

In summary, is this possible to, in the middle of a formula, store
something in memory and recall it from memory for use in other places in
the formula?

I think you'll say no, that's what VBA is for. That's ok, I just would
like to know for sure.

Thanks,

Harold

reducing the number of times an IF statement needs to be calculated in embedded IF statements
 

Harold,

Could you explain what you're trying to do with the formula? It
obviously involves some sort of manipulation of dates, and it may
be that you could simplify it by using Excel's built-in date functions.

To answer your original question, I'm pretty sure that it's not
possible to do what you ask within a worksheet function. A
simple way to avoid repetition and deep nesting is to add an
extra column or two to hold intermediate results, and hide
these columns if they spoil the presentation of the worksheet.

hth
Andrew Taylor


Harold Good wrote:
Hi,

I have a long formula that is getting too long. It has 3 embedded IF
statements, with the logical test below having to be calculated each time.
(Planning!X14/365.25)+1900-INT((Planning!X14/365.25)+1900)

For example, when the logical test solves this to 2004, is there some way to
store that "2004" in memory the first time and recall it as "2004" in the
formula for the other two logical tests. Otherwise the formula is getting so
long, and these three embedded IF are just a portion of a longer formula.

In summary, is this possible to, in the middle of a formula, store something
in memory and recall it from memory for use in other places in the formula?

I think you'll say no, that's what VBA is for. That's ok, I just would like
to know for sure.

Thanks,

Harold

reducing the number of times an IF statement needs to be calculatedin embedded IF statements
 

Hi Harold

One way would be to use a cell say X1 to hold this calculation, then
reference $X$1 in your formula in place of the calculation.

Regards

Roger Govier


Harold Good wrote:
Hi,

I have a long formula that is getting too long. It has 3 embedded IF
statements, with the logical test below having to be calculated each time.
(Planning!X14/365.25)+1900-INT((Planning!X14/365.25)+1900)

For example, when the logical test solves this to 2004, is there some way to
store that "2004" in memory the first time and recall it as "2004" in the
formula for the other two logical tests. Otherwise the formula is getting so
long, and these three embedded IF are just a portion of a longer formula.

In summary, is this possible to, in the middle of a formula, store something
in memory and recall it from memory for use in other places in the formula?

I think you'll say no, that's what VBA is for. That's ok, I just would like
to know for sure.

Thanks,

Harold

reducing the number of times an IF statement needs to be calculated in embedded IF statements
 

It has to do with a five digit number such as 20042, where the final digit
is the quarter. So I have to pull out the 2004 and do that formula. But like
you and others have suggested, storing this 2004 in a separate cell is a
simpler way to go.

Thanks,

Harold


"Andrew Taylor" wrote in message
oups.com...
Harold,

Could you explain what you're trying to do with the formula? It
obviously involves some sort of manipulation of dates, and it may
be that you could simplify it by using Excel's built-in date functions.

To answer your original question, I'm pretty sure that it's not
possible to do what you ask within a worksheet function. A
simple way to avoid repetition and deep nesting is to add an
extra column or two to hold intermediate results, and hide
these columns if they spoil the presentation of the worksheet.

hth
Andrew Taylor


Harold Good wrote:
Hi,

I have a long formula that is getting too long. It has 3 embedded IF
statements, with the logical test below having to be calculated each
time.
(Planning!X14/365.25)+1900-INT((Planning!X14/365.25)+1900)

For example, when the logical test solves this to 2004, is there some way
to
store that "2004" in memory the first time and recall it as "2004" in the
formula for the other two logical tests. Otherwise the formula is getting
so
long, and these three embedded IF are just a portion of a longer formula.

In summary, is this possible to, in the middle of a formula, store
something
in memory and recall it from memory for use in other places in the
formula?

I think you'll say no, that's what VBA is for. That's ok, I just would
like
to know for sure.

Thanks,

Harold