Greater than/Less than (<>) working back to front with an IF funct

[Jay]

Hey there,

If i use the following 'should be simple' formula:

=IF(SUM(E137:E139)>E141,"yes","no")

and the numbers in the sum range are bigger than the number in the e141
field, excel says "no". If i reverse the > so its a < it says "yes".

Whats going on!!! Im using excel 2003 with all the updates possible and im
hitting my head against a brick wall here. I have been over this so many
times and i just dont understand.

Thanks,

Jay.

 

[RWN]

Is E141 a text value perhaps?

 

[Jay]

No, its a number. I checked that one.

Can you replicate the problem at least? I have tried multiple computers and
i get the same problem.

Jay.

 

[garfield-n-odie]

Are you dealing with negative numbers? If so, then -3 is greater
than -5, for example.

 

[RWN]

The only way I can replicate it is if the cell (E141) is formatted as text.
When you say you checked for a number, did you check the formatting?

 

[garfield-n-odie]

So what exactly do the cells E137, E138, E139, and E141 contain?
If values, what ones? If formulas, what are they?

 

[Jay]

just numbers!!!

ok - now this is very strange - i just tried changing the formula to this:

=IF((E137+E138+E139)>E141,"yes","no")

and it works as it should do! is there something wrong with the sum
function and the <>??

Jay.

 

[garfield-n-odie]

<sigh> "just numbers!!!" is not enough information for me to
attempt to reproduce your problem, but you've obviously found a
workaround already. Maybe next time when people try to help you,
you won't waste their time and just give them a straight answer
to a simple question. Good luck.

 

[Jay]

im sorry if you are frustrated by what i wrote. when i said 'just numbers' i
meant that literally. the fields are there for people to put in how many
sales they have of each product. so they type numbers in. nothing specific
- just numbers.

like 1 or 2 or 3 or 4 etc

in my understanding i did give you a straight answer.

 

[RWN]

Jay;
I haven't tried this on xl'03 but will do it tomorrow (at work) although I'm sure I would
have seen it by now.

What *exactly* is in E141 - an entered number, formula etc etc. If it's a formula, what is
the formula and what does it equate to?
What are the entered numbers in the "Sum" range.

You have to remember that it is very important to state the precise conditions surrounding
a problem because there are many things that can cause unintended results.
If E141, for example, was a text value (even though appearing as a number) it would screw
things up (text values are higher in the collating sequence than numerics) - this was my
1st guess-which you have discounted.

I have found that whenever I've hit something that makes no sense that it means I've
missed something vs. the application being in error (esp. with straightforward functions
such as this).
Granted there are some things I've learned about the hard way (Excel's limits on precision
with decimal numbers given it is a binary numbering system) but, for the most part, it's
usually my fault.

I'll post back tomorrow.

 

[RWN]

As promised-I tried it today at work (xl'03) and no problem.