Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Remove IF? (Excel-97-SR2)

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

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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