Excel is telling me 10 is not greater than 9!!

[locopete999]

Hi Guys,

I have a problem and would like an extra pair of eyes.

To help, I have detailed my exact set up below.

I have 2 cells of data.

Cell B10 has the amount of days an order has been delayed. B10 equals 10

Call C10 has the last action taken. The first number in the cells was the number of days it was delayed when the last action was taken. C10 equals 9 POT SENT 04/08/15

Cell D10 gives me just the days from cell C10, either 1 digit or 2, depending on whether the 2nd "letter" is a space or not.

Please Login or Register  to view this content.

Cell E10 looks at cell B10 to tell me if it is proceeded by a minus (-) if it is, then it returns a 1, if not a zero

Please Login or Register  to view this content.

Cell F10 returns the value in cell B10, but without the (-) if its a negative number.

Please Login or Register  to view this content.

This all leads up to cell G10
What I am trying to get Excel to work out is if Cell B10 is greater or lesser than the value in cell C10. To this end, I have employed formulae to work out the following:

1) if the number is not a minus (E10=0) then work out if B10 is greater than D10.
2)If the number is a minus (E10=1) then work out if B10 is greater than D10 + F10 (the positive version of B10)

Please Login or Register  to view this content.

The thinking behind this is that Excel is not taking + or - numbers into account on this sheet so -5 is being reported as greater than 6, so if I add them together it will report that -5 is not greater than 11.

However, using the exact setup above, Excel is telling me that 10 is not greater than 9.



Can someone have a look at this and let me know where this is going wrong, or, if you have one, a simpler solution.

Many Thanks

Pete

[Pete_UK]

You should know that using string functions like LEFT, RIGHT etc. will return text values and not numbers, so you are probably trying to compare a text value with a number. You can use the ABS function on a number to return just the magnitude of the number (whether it is positive or negative). Also, you can use the SIGN function to determine if it is positive or negative (or zero). In addition, if you have a text value that looks like a number, you can carry out some simple arithmetic on it like adding zero or multiplying by 1 (or using the VALUE function) to convert it into a numerical value.

Hope this helps.

Pete

[locopete999]

Hi Pete,

Thanks for the reply.

As far as I could I've made sure that this is recognised as numbers. I have a line above the example given where the B10 value is 6 and the D10 value is 5, and it is giving me the right answer. This has lead me to believe that its not an issue with text values and not numbers.
I'll try running this again but taking your reply into account. I'll keep you posted!!

[Jonmo1]

Pete's right.

If D10 contains this formula
=IF(RIGHT(LEFT(C10,2),1)=" ",LEFT(C10,1),LEFT(C10,2))
Then D10 is NOT a number, even though it may look like one.
And even though you may have formatted that cell as a number.

The LEFT function returns a TEXT string, even if it looks like a number.

'Proof is in the pudding' so they say.
This returns FALSE
=ISNUMBER(D10)

You have to convert that result to a real number using any of the methods Pete described.

For D10, try
=IF(RIGHT(LEFT(C10,2),1)=" ",LEFT(C10,1)+0,LEFT(C10,2)+0)

[Special-K]

You should be comparing number gainst number, not number against text.

If the result is a text value add 0 to it to force it into a number.
e.g.
=LEFT("123ABCD",3)
result is text 123

=LEFT("123ABCD",3)+0
result is 123 but is a number

[locopete999]

Hi Pete and Jonmo1,

I have reworked my sheet with Pete's methods and I have it working!

Thanks to you both for your help, rep will be coming your way.

I think as Jonmo1 put it, I didn't take into account that formatting it to look like a number doesn't make it one!

[locopete999]

Thanks Special-K, rep sent your way as well!

[Jonmo1]

You're welcome.

I think as Jonmo1 put it, I didn't take into account that formatting it to look like a number doesn't make it one!

A VERY important rule to understand in Excel, and often overlooked.

Also, a subtext of that rule is that formatting a value in a certain way only changes that value's 'appearance'
The actual value in the cell remains unchanged.

i.e. Formatting 12.3456 to show 3 decimals will appear to be 12.346.
But the actual value in the cell is still 12.3456

[Jonmo1]

FYI,

Here's a much simpler way to capture the number in C10
=LEFT(C10,2)+0

This is a nice thing about mathmatical operations in Excel (+0)
It will take anything that looks like a number, and convert it to one.
So even if the number in C10 is only 1 digit, then LEFT(C10,2) returns "9 "
That space is considered superfleous by the +0 and is ignored.

So if it's known that the number will never be more than 2 digits, and it will always be followed by a space, then this is sufficient.

[locopete999]

Thanks Jonmo1, always love to learn new ways to do things and that's going to save me from having to write a lot of IF's!