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.

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([@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. etafModerator

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

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

4. etafModerator

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

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. etafModerator

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

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

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

8. etafModerator

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

and see if you can get a T or F

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

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

11. etafModerator

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

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

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. etafModerator

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 ?

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