Integer problem

  • Thread starter Thread starter NOTTNICK
  • Start date Start date
N

NOTTNICK

I have produced a spreadsheet that works brilliantly.
(basically I am separating a decimal fraction from the whole number)
However I am getting an integer problem.

The formula in cell Y7is = T7-H7
T7 has the value -0.58
H7 has the value -1.58
This means that Y7 shows the value 1.00 (Which is what I want)
However, my formula
=IF(Y7>=0,(Y7-INT(Y7)),((INT(Y7)+1)-Y7))
(in Z7)

Gives me a value of 1
It should be 0

If I put the value 1.00 in cell Y7
I get the correct value (0).

Therefore, I get 2 completely different answers from the same input
values.
The only difference is that one of them is obtained from the
subtraction of 2 negatives.

I hope this makes sense.

Any ideas?

Nick
 
I put your values and formulas into an empty sheet and I got 0 in Z7.

So I'm gonna guess that the values in T7 or H7 (or both) aren't exactly what you
posted.

I'm gonna guess that they're very close to those values and display what you
posted with the number format set the way you have it.

You can test this by selecting T7 and hitting F2, then F9.
Then look at the formula bar.

Do you see -0.58 or something else?

How about when you do the same thing with H7 and even Y7?

You may be able to see the differences by widening the column -- or even showing
more decimal positions in the number format.
 
I put your values and formulas into an empty sheet and I got 0 in Z7.

So I'm gonna guess that the values in T7 or H7 (or both) aren't exactly what you
posted.

I'm gonna guess that they're very close to those values and display what you
posted with the number format set the way you have it.

You can test this by selecting T7 and hitting F2, then F9.
Then look at the formula bar.

Do you see -0.58 or something else?

How about when you do the same thing with H7 and even Y7?

You may be able to see the differences by widening the column -- or even showing
more decimal positions in the number format.
Click to expand...

Aaah
-0.583333333333334
and
-1.58333333333333

Is there a way I can limit the number of decimal places excel works
with?

Nick
 
You can limit the number of decimals by
Tools|Options|Calculation tab|check Precision as displayed
and format the cells the way you want.

But I wouldn't do this.

Instead, I'd add
=round()
or
=roundup()
or
=rounddown()

to those other formulas to get what you want.

You could also do something like:

=if(abs(t7-h7)<0.000001, ...

A very small difference.
 
You can limit the number of decimals by
Tools|Options|Calculation tab|check Precision as displayed
and format the cells the way you want.

But I wouldn't do this.

Instead, I'd add
=round()
or
=roundup()
or
=rounddown()

to those other formulas to get what you want.

You could also do something like:

=if(abs(t7-h7)<0.000001, ...

A very small difference.
Click to expand...

What I have done is this
=IF((T7-H7)<0,(T7-H7)-0.0001,(T7-H7)+0.0001)

Then it works.

Really appreciate your help.
Nick
 
What I have done is this
=IF((T7-H7)<0,(T7-H7)-0.0001,(T7-H7)+0.0001)
Click to expand...

Would you prefer to move H7 to the right hand side of your equation?

IF(T7 < H7, etc...

= = = = = =
Dana DeLouis
 
What I have done is this
=IF((T7-H7)<0,(T7-H7)-0.0001,(T7-H7)+0.0001)

Then it works.

Really appreciate your help.
Nick
Click to expand...

Prefer your method, have changed it to that.
Nick
 
Prefer your method, have changed it to that.
Nick
Click to expand...

Missed Dana's reply,
Thanks for that suggestion, but the =ROUND((T7-H7),2) is what I have
gone with.
Works perfectly.

Nick
 
Back
Top