Solved Excel 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

Thread Starter
Joined
Sep 6, 2018
Messages
41
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))))
 

etaf

Moderator
Joined
Oct 2, 2003
Messages
65,435
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))
 

etaf

Moderator
Joined
Oct 2, 2003
Messages
65,435
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
 

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
41
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
Joined
Oct 2, 2003
Messages
65,435
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]]
 

etaf

Moderator
Joined
Oct 2, 2003
Messages
65,435
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
 

etaf

Moderator
Joined
Oct 2, 2003
Messages
65,435
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
 
Joined
Jul 9, 2018
Messages
561
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
 

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
41
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
Joined
Oct 2, 2003
Messages
65,435
First Name
Wayne
=IF(HT2=0,0,IF(OR(II2="Locked-In",II2="Financial Review"),SUM(GU2/HT2*HW2),0))

does that work ?
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top