# Excel Simple Problem

Discussion in 'Business Applications' started by CallMeAndy, Oct 17, 2011.

Not open for further replies.

Joined:
Jun 9, 2005
Messages:
13
This must be me having a bad day!

Both L2 and L35 are Number fields with 2 decimals places.

Cell L3 has:
=IF(L2<>L35,L2-L35,"")

Obviously it should return the difference or a blank cell.

Wrong its returning 0.00

Any light on this appreciated.

2. ### Pedro15

Joined:
Oct 5, 2008
Messages:
386
What is cell formatted as.

What are numbers in the 2 cells so I can try and replicate.

Pedro

Joined:
Jun 9, 2005
Messages:
13
The numbers can be anything: they change every few days but the problem persists.

Currently L2 and L35 are both Zero.

Yesterday they were both 17.25

As i said they are both formatted as Numbers with 2 decimal places.

I should have made explicit that its displaying 0.00 when the two cell values are equal, which is when it should display as ""

Also neighboring cells have the same formula allowing for the offset ( i.e k2 instead of L2 etc) and they do not exhibit this behaviour.

4. ### Pedro15

Joined:
Oct 5, 2008
Messages:
386
Sorry but mine works perfectly.

Are there any macros involved.
What determines what goes into the two cells?

Bit of a mystery.

Pedro

Joined:
Jun 9, 2005
Messages:
13
Ok you have made me wake up, good man, but its a case of a very annoying circumstance, in that I am pretty sure I must have been the culprit.

There are no macros involved L2 is just a value from direct keyboard input
L35 is =Sum(L9:L34)
L9:L34 should all be simple keyboard entries - nothing to go wrong!

Except it transpires that L9 appears to be 0.00 (an opening balance formatted as a number with 2 decimal places) but has in fact got -2.8421709430404E-13 as the actual value.

I dont know how that got in there but I did know it was a bad day!

Thanks Pedro

6. ### Pedro15

Joined:
Oct 5, 2008
Messages:
386
Good to hear. Now we can both have a good night's sleep.

Pedro

As Seen On