# SolvedExcel Multiple If statements - want zero instead of false result

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### Gevans0666

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([@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))))

#### etaf

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

#### Gevans0666

Tried it... still get a FALSE result

#### etaf

Moderator
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
which may be the issue

#### Gevans0666

No - I actually caught that error first time round... the formula you gave works correctly- just still gives a FALSE return instead of zero.

#### etaf

Moderator
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

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

#### Gevans0666

I get the word false - I want a zero

#### etaf

Moderator
you will have to test a few things out
or upload a sample file here

and see if you can get a T or F

#### Gevans0666

I did -- all turn to F

#### Gevans0666

Formula is in column HE

#### etaf

Moderator
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

#### Chawbacon

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

#### Gevans0666

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

#### etaf

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

does that work ?

#### Gevans0666

OMG!!!!!!!!! That did it!!!!!!!!!!!!! Thank you guys soooo much!!!!!!!!!!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On