1. ## Remove IF? (Excel-97-SR2)

Is there a neater/shorter way to write:

=IF(ISNUMBER(Actuals!E6),Actuals!E6,Plan!D6*TrackB yException)*D\$4

In essence what it says is.. If EVER there is a number in Actuals!E6 use it otherwise, use the number in Plan!D6 multiplied by logic value TrackByException (0/1). Finally, whatever the result, multiply it by the logic value held in D\$4 (0/1). i.e. 0 may well be a valid result.

This formula is repeated in 3,000+ cells (cell refs change in each) and I'm just curious if there is a neater way to write it.

Regards
Peter

2. ## Re: Remove IF? (Excel-97-SR2)

Hi Peter,

Beauty is in the eye of the beholder.

If you want to eliminate the IF function, you could use:
=(ISNUMBER(Actuals!E6)*Actuals!E6+(1-ISNUMBER(Actuals!E6))*Plan!D6*TrackByException)*D\$ 4

No IF, but not shorter either. As to whether it's any neater, I'll leave you to decide.

Cheers

3. ## Re: Remove IF? (Excel-97-SR2)

Or, perhaps a bit simpler/shorter:
=(MAX(Actuals!E6)+(1-ISNUMBER(Actuals!E6))*Plan!D6*TrackByException)*D\$ 4

4. ## Re: Remove IF? (Excel-97-SR2)

Well, you could define a name with that formula and then use the name in the sheet. But it isn't making the formula any shorter.

5. ## Re: Remove IF? (Excel-97-SR2)

Much to play with! Maybe I'll suffer the IF's.... Hope Steve isn't listening. ;-)

Regards
Peter

6. ## Re: Remove IF? (Excel-97-SR2)

I'm always listening.

My comment wasn't meant as a blanket condemnation of IFs, I use them frequently. Just be aware, they are memory hogs and may cause sluggishness.

Steve

7. ## Re: Remove IF? (Excel-97-SR2)

Humour translates so poorly in here....

As an aside, and in support of your advice, I took over 2.5Mb off my 5.91Mb model by applying your suggestions about removing/reducing the use of IF's.

Regards
Peter

8. ## Re: Remove IF? (Excel-97-SR2)

Jan Karel... Can I double check that I'm not missing some subtley of Excel that I haven't previously understood? Can you explain what you mean when you say "define a name with that formula and then use the name in the sheet"? It sort of implies that I can enter a formula into a single cell, give it a name and then access that formula from anywhere AND pass it parameters? I had thought you could only do this via VBA?

Regards
Peter

9. ## Re: Remove IF? (Excel-97-SR2)

Take my advice litterally: enter the FORMULA in the refersto box of the name, not a cell/range of cells. See attached workbook for an example.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•