nested if statement when cell formula is based on contains a zero

Question

I am trying to run an iIFstatement where I want to return an answer based on the value of comparing 2 cells D3 and E3.  So if E3 is less than D3 it shows Above Target , if E3 is equal to D3 it shows On Target and if E3 is greater than D3 is shows Below Target.  I can do this ok.  However some of the cells contain a 0 (which I have subsequently hidden using Start, Excel, advanced options, display options.  I therefore want it to calculate if either of the cells D3 and E3 are blank (or a hidden 0) then I want it to show nothing using "".

I have tried the formulae below with no success.

=IF(E3<D3,"Above Target", IF(E3=D3, "On Target", IF(E3>D3, "Below Target", IF(E3=isblank, "", IF(D3=isblank,"")))))

=IF(E3<D3,"Above Target", IF(E3=D3, "On Target", IF(E3>D3, "Below Target", IF(E3=0, "", IF(D3=0,"")))))

=IF(E3<D3,"Above Target", IF(E3=D3, "On Target", IF(E3>D3, "Below Target", IF(E3=zero "", IF(D3=zero,"")))))

There is an added complicaton (or maybe it isn't?) the data which I want to perform the IF statement on, in my work sheet ,is being brought into the front worksheet from a linked worksheet (within same workbook) using for example ='RAW Data'!E3.  Not sure if this makes a difference.

Anyway any help greatly received.

Thanks

Nicola.

Answer 1

Hi,

Try this

=IF(E3<D3,"Above Traget",IF(E3>D3,"Below target","On target"))

and with sheet ref's

=IF('Raw Data'!E3<'Raw Data'!D3,"Above Traget",IF('Raw Data'!E3>'Raw Data'!D3,"Below target","On target"))

Answer 2

Try this regular formula:

=IF(AND(D3:E3),LOOKUP(SIGN(D3-E3),{-1,0,1},{"Below","On","Above"})&" target","")

EDITED TO INLCUDE THIS:

Better formula...this one catches a blank in either/both cells and returns an empty string:

=IF(AND(N(D3),N(E3)),LOOKUP(SIGN(D3-E3),{-1,0,1},{"Below","On","Above"})&" target","")

More edits :\

Examples of returned values:

Col_D     Col_E      Returned values

  9          10             Below target

11          10             Above target

10          10             On target

  0          10        

  9            0        

  0            0        

(blank)   (blank)

=""       =""

TEXT      TEXT        

Does that help?

Answer 3

=IF(D3*E3=0,"",IF(E3<D3,"Above Target",IF(E3=D3,"On Target",IF(E3>D3,"Below Target"))))