2 check if statement

**richy1066** · 10-08-2010, 07:45 AM

Hello all,

I'm trying to do a 2 check statement, and i nearly have it, but the last bit is beating me and i'm confused as to how tofix it.

my formula is:

=IF(Input!D5-Input!E5=0, ROUND((Input!E5/(Input!O2/100+1)),2),IF(Input!G5 =1,ROUND((Input!E5/(Input!O2/100+1)+Input!E5),2),0))

the 2 checks i need are:
1. Input!D5-Input!E5=0
2. Input!G5 =1

the first bit seems to be ok, if Input!D5-Input!E5=0 it does ROUND((Input!E5/(Input!O2/100+1)),2). If it doesnt = 0 it does ROUND((Input!E5/(Input!O2/100+1)+Input!E5

but the second criteria seems to be letting me down, if Input!G5 is blank it is still outputting a calculated value into the cell, not the 0 i want for a false result on the if.

hope the above makes sence. any help would be very very greatfully recieved.

**NBVC** · 10-08-2010, 08:12 AM

It only calculates that if D5-E5 is still equal to 0... otherwise, if G5 is blank you should get 0.... Maybe you need to reverse your condtions.. the nested IFs.. work from left most to right-most.

**richy1066** · 10-08-2010, 09:49 AM

doesnt seem to be doing what i'm after.

this is where i wish excel has sql type 'then / else' commands. if Input!G5 =1 i want it to start the next set checks which is Input!D5-Input!E5=0. if Input!G5 is blank or anything but 1 i just want it reporting 0 and not bother carrying on with the next checks etc

so if Input!G5 =1 then do the check IF(Input!D5-Input!E5=0

if IF(Input!D5-Input!E5=0 is true, calc it this way: ROUND((Input!O2/(Input!F3/100+1)),2)
but
if (Input!D5-Input!E5=0 is false, calc it this way: ,ROUND((Input!O2/(Input!F3/100+1)+Input!G5),2

not sure how to structure the nested if's or if this is even possible or a good way to do it

**NBVC** · 10-08-2010, 10:35 AM

Try:

=IF(Input!G5=1,IF(Input!D5-Input!E5=0, ROUND((Input!E5/(Input!O2/100+1)),2),ROUND((Input!E5/(Input!O2/100+1)+Input!E5),2)),0)

or

=IF(Input!G5=1,ROUND((Input!E5/(Input!O2/100+1)+IF(Input!D5-Input!E5=0,0,Input!E5)),2),0)