Integer problem

[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

 

[Dave Peterson]

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.

 

[NOTTNICK]

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.

Aaah
-0.583333333333334
and
-1.58333333333333

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

Nick

 

[Dave Peterson]

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.

 

[NOTTNICK]

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.

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

 

[Dana DeLouis]

What I have done is this

=IF((T7-H7)<0,(T7-H7)-0.0001,(T7-H7)+0.0001)

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

IF(T7 < H7, etc...

= = = = = =
Dana DeLouis

 

[NOTTNICK]

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

Prefer your method, have changed it to that.
Nick

 

[NOTTNICK]

Prefer your method, have changed it to that.
Nick

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

Nick