1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Excel Multiple If statements - want zero instead of false result

Discussion in 'Business Applications' started by Gevans0666, Jan 12, 2019 at 1:05 PM.

Advertisement
  1. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    I have the following 5 Status's... only 2 require a calculated result:
    1. Financial Review
    2. Future Wave
    3. In Progress
    4. Locked-In
    5. Cancelled

    For the following formula - I want anything that has a status of Financial Review or Locked In to run a formula for a dollar value result -- otherwise, I want a zero. Right now I get a FALSE response.

    I spread out formula below for easier reading. Here is the Formula I have now - which works except for the FALSE where I want a value of zero:

    IF([@[Effective - Adjusted]]="0","0",

    IF([@Status]="Locked-In",SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]],0),

    IF([@Status]="Financial Review",SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]],0))))
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,876
    First Name:
    Wayne
    same equation is used - so you can use an OR

    IF ( OR ( [@Status]="Locked-In" , [@Status]="Financial Review") , SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]] , 0)

    Note "0" is text and not a zero
    just use 0 to get the number 0 rather than a letter zero - if that makes sense

    if that was all one formula
    so
    F([@[Effective - Adjusted]]=,0, 0, IF ( OR ( [@Status]="Locked-In" , [@Status]="Financial Review") , SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]] , 0))
     
  3. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    Tried it... still get a FALSE result :(
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,876
    First Name:
    Wayne
    try

    IF([@[Effective - Adjusted]]=0, 0, IF ( OR ( [@Status]="Locked-In" , [@Status]="Financial Review") , SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]] , 0))

    comma before the first 0
    @[Effective - Adjusted]]=,0
    which may be the issue
     
  5. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    No - I actually caught that error first time round... the formula you gave works correctly- just still gives a FALSE return instead of zero.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,876
    First Name:
    Wayne
    ok,
    this is how it should work as a nested IF

    IF( test, True , False)

    and we have added another IF as a nested IF in false

    IF ( test , TRUE , IF ( test, TRUE , FALSE ))

    IF([@[Effective - Adjusted]]=0, 0, IF ( OR ( [@Status]="Locked-In" , [@Status]="Financial Review") , SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]] , 0))

    I would test each bit and see where its failing

    JUST checking when you say FALSE do you mean you get the word FALSE in the cell or a zero ?

    test each section in different cells and for conditions
    I dont no what cells these refer to - you may want to change the table names to actual cells

    @[Effective - Adjusted]]=0
    OR ( [@Status]="Locked-In" , [@Status]="Financial Review")
    SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]]
     
  7. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    I get the word false - I want a zero
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,876
    First Name:
    Wayne
    you will have to test a few things out
    or upload a sample file here

    I would start with in a seperate cell
    =IF([@[Effective - Adjusted]]=0, "T", "F")
    and see if you can get a T or F
     
  9. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    I did -- all turn to F
     
  10. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    Formula is in column HE
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,876
    First Name:
    Wayne
    i changed the formula to
    =IF(HT2=0,0,IF(OR(II2="Locked-In",II2="Financial Review"),"SUM",0))
    and it works - so something maybe wrong with
    SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]]

    i'm not keen on using the table referewnce I prefer to use the actual cell values references
    can you change

    SUM([@[Reforecast OPEX-SAV]]/[@[Effective - Adjusted]]*[@[Effective Current Fiscal]]
    to the cell reference in row 2
     
  12. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    229
    First Name:
    Jack
    I must agree with @etaf concerning this issue for formula references and configuration of the IF statement.

    Just took a quick look at your formula within the spreadsheet, and it appears that the last zero may need to be shifted one ending bracket to the right.

    good luck
     
  13. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    Wht do you mean " the last zero may need to be shifted one ending bracket to the right."?

    =IF(HT2=0,0,IF(OR(II2="Locked-In",II2="Financial Review"),SUM(GU2/HT2*HW2,0)))
     
  14. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,876
    First Name:
    Wayne
    =IF(HT2=0,0,IF(OR(II2="Locked-In",II2="Financial Review"),SUM(GU2/HT2*HW2),0))

    does that work ?
     
  15. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    29
    OMG!!!!!!!!! That did it!!!!!!!!!!!!! Thank you guys soooo much!!!!!!!!!!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1221780

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice