Returning the value in one cell when a condition isn't met in another

[Simon Lee]

Hi there

Been a while and I'm struggling on a particular problem.

          A            B             C             D
1     ALLOCATION  ACTUAL TEAM     PENALTY    PENALTY
2  FOR 2015 NNT FOR 2015 NNT CAPPED 2016 CAPPED 2016
3          35         34       -1.00       =B3
4          21         23        2.00         0

The formula I am using to produce a result in Cell D3 is =IF(B3<A3,"=B3","0")

At the moment I get the TEXT B3 in D3 when B3 is less than A3 when I actually want it to return the VALUE in Column B3, being the number 34.

When B3 is equal to or more than A3 I correctly get the desired numerical return of 0

What am I missing here, I can't see the wood for the trees right now

 

[NARAYANK991]

Hi ,

I had made a comment on an earlier question about this ; never use text strings for quantities which are in reality numeric.

Your formula , whether right or wrong , is a second issue ; first , it should be changed to :

=IF(B3<A3,B3,0)

When this is entered in cell D3 , what you are telling Excel to do is this :

Compare the contents of cell B3 and A3

If the content in B3 is less than that in A3 , then put what ever is in B3 in D3

Else , which means , if the content in B3 is greater than or equal to that in A3 then , put 0 in D3.

Narayan

 

[Simon Lee]

Narayan, bang on the mark, many thanks dude