calculating with displayed values

I have two cells, each displaying numbers to with 2 decimal points

Cell A1 contains the number 15.333333333 (the figure the result of a
calculation)

Cell A2 contains the number 15.33 (the figure is input by the user)

Both will display 15.33

I want to set up cell A3 to warn me if there is a difference in the
displayed values

If I use =IF(A1-A2=0,"","check!"), it will always show check!, because the
actual values of A1-A2 is not 0

How do I adjust the formula so the calculation is between the displayed
values (i.e. 15.33-15.33 = 0) not the actual values ?

Many thanks


--
Tobit Curteis
________________________________

Tobit Curteis Associates
36 Abbey Road
Cambridge
CB5 8HQ
UK

Tel: +44 (0)1223 501958
Fax: +44 (0)1223 304190
E-mail:
Web: www.tobit-curteis-associates.com

"Tobit" wrote in message
...
I have two cells, each displaying numbers to with 2 decimal points

Cell A1 contains the number 15.333333333 (the figure the result of a
calculation)

Cell A2 contains the number 15.33 (the figure is input by the user)

Both will display 15.33

I want to set up cell A3 to warn me if there is a difference in the
displayed values

If I use =IF(A1-A2=0,"","check!"), it will always show check!, because the
actual values of A1-A2 is not 0

How do I adjust the formula so the calculation is between the displayed
values (i.e. 15.33-15.33 = 0) not the actual values ?

Many thanks


--
Tobit Curteis

Maybe this is what you want:
=IF(ROUND(A1,2)-A2=0,"","check!")

Or maybe you should change the formula in A1 to incorporate the rounding:
=ROUND(yourformula,2)
where yourformula is the formula you had before.

It depends on what you are trying to achieve.

On Tue, 16 Sep 2003 10:33:52 +0000 (UTC), "Tobit" wrote:

I have two cells, each displaying numbers to with 2 decimal points

Cell A1 contains the number 15.333333333 (the figure the result of a
calculation)

Cell A2 contains the number 15.33 (the figure is input by the user)

Both will display 15.33

I want to set up cell A3 to warn me if there is a difference in the
displayed values

If I use =IF(A1-A2=0,"","check!"), it will always show check!, because the
actual values of A1-A2 is not 0

How do I adjust the formula so the calculation is between the displayed
values (i.e. 15.33-15.33 = 0) not the actual values ?

Many thanks

If the user entered number will always have two decimal places, then:

=A2=ROUND(A1,2)

will return TRUE in the situation you posit.

If the user can enter a variable number of decimal places, then you need a more
complex formula to determine how many decimal places the user entered. The
*array-entered* formula:


=A2=ROUND(A1,MATCH(TRUE,A2*10^ROW(INDIRECT("1:15") )=INT(A2*10^ROW(INDIRECT("1:15"))),0))

should do that. To **array-enter** a formula, after typing or pasting it in,
hold down ctrlshift while hitting enter. XL will place braces {...}
around the formula.

To use the equality in your IF statement, either:

=IF(A2=ROUND(A1,2),"","check!")

or, (again **array-enter**):

=IF(A2=ROUND(A1,MATCH(TRUE,A2*10^ROW(INDIRECT("1:1 5"))=INT(A2*10^ROW(INDIRECT("1:15"))),0)),"","chec k!")


--ron

Its the ROUND bit that I was trying to understand - with this info, I can
solve the basic problem - many thanks all.

One follow up question

If the resultant value does not = 0, the present response is to display
check!. How would I make the respose to display the resultant value itself

e.g

A1=5.10
A2=5.00

=IF(ROUND(A1,2)-A2=0,"","check!")

Present response = Check!
The response I would like is 0.1

Tobit


Maybe this is what you want:
=IF(ROUND(A1,2)-A2=0,"","check!")

Or maybe you should change the formula in A1 to incorporate the rounding:
=ROUND(yourformula,2)
where yourformula is the formula you had before.

It depends on what you are trying to achieve.

On Tue, 16 Sep 2003 13:34:45 +0000 (UTC), "Tobit" wrote:

Its the ROUND bit that I was trying to understand - with this info, I can
solve the basic problem - many thanks all.

One follow up question

If the resultant value does not = 0, the present response is to display
check!. How would I make the respose to display the resultant value itself

e.g

A1=5.10
A2=5.00

=IF(ROUND(A1,2)-A2=0,"","check!")

Present response = Check!
The response I would like is 0.1

Tobit


=IF(A2=ROUND(A1,2),"",A1-A2)


--ron

You would replace "check!" in your formula with ROUND(A1,2)-A2. This would
give
=IF(ROUND(A1,2)-A2=0,"",ROUND(A1,2)-A2)

But then, if you think about it, you don't need the IF statement. You just
use the formula
=ROUND(A1,2)-A2
and turn off the display of zero values.

"Tobit" wrote in message
...
Its the ROUND bit that I was trying to understand - with this info, I can
solve the basic problem - many thanks all.

One follow up question

If the resultant value does not = 0, the present response is to display
check!. How would I make the respose to display the resultant value itself

e.g

A1=5.10
A2=5.00

=IF(ROUND(A1,2)-A2=0,"","check!")

Present response = Check!
The response I would like is 0.1

Tobit


Maybe this is what you want:
=IF(ROUND(A1,2)-A2=0,"","check!")

Or maybe you should change the formula in A1 to incorporate the
rounding:
=ROUND(yourformula,2)
where yourformula is the formula you had before.

It depends on what you are trying to achieve.

Tobit

Try using
=IF(ROUND(A1,2)-A2=0,"",ROUND(A1,2)-A2)

Andy.

"Tobit" wrote in message
...
Its the ROUND bit that I was trying to understand - with this info, I can
solve the basic problem - many thanks all.

One follow up question

If the resultant value does not = 0, the present response is to display
check!. How would I make the respose to display the resultant value itself

e.g

A1=5.10
A2=5.00

=IF(ROUND(A1,2)-A2=0,"","check!")

Present response = Check!
The response I would like is 0.1

Tobit


Maybe this is what you want:
=IF(ROUND(A1,2)-A2=0,"","check!")

Or maybe you should change the formula in A1 to incorporate the
rounding:
=ROUND(yourformula,2)
where yourformula is the formula you had before.

It depends on what you are trying to achieve.

If you *always* want to use the value as displayed, you can set up Excel to
do so. Tools-Options-Calculation-Precision as Displayed.

If you want to do so on a case by case basis, use the Round function.
=Round(46/3, 2).

HTH
-Dave


"Tobit" wrote in message
...
I have two cells, each displaying numbers to with 2 decimal points

Cell A1 contains the number 15.333333333 (the figure the result of a
calculation)

Cell A2 contains the number 15.33 (the figure is input by the user)

Both will display 15.33

I want to set up cell A3 to warn me if there is a difference in the
displayed values

If I use =IF(A1-A2=0,"","check!"), it will always show check!, because the
actual values of A1-A2 is not 0

How do I adjust the formula so the calculation is between the displayed
values (i.e. 15.33-15.33 = 0) not the actual values ?

Many thanks


--
Tobit Curteis
________________________________

Tobit Curteis Associates
36 Abbey Road
Cambridge
CB5 8HQ
UK

Tel: +44 (0)1223 501958
Fax: +44 (0)1223 304190
E-mail:
Web: www.tobit-curteis-associates.com

Works perfectly

Many thanks

--
Tobit

Use Exact()

=if(EXACT(A1,A2),"check","")

"Tobit" wrote in message ...
I have two cells, each displaying numbers to with 2 decimal points

Cell A1 contains the number 15.333333333 (the figure the result of a
calculation)

Cell A2 contains the number 15.33 (the figure is input by the user)

Both will display 15.33

I want to set up cell A3 to warn me if there is a difference in the
displayed values

If I use =IF(A1-A2=0,"","check!"), it will always show check!, because the
actual values of A1-A2 is not 0

How do I adjust the formula so the calculation is between the displayed
values (i.e. 15.33-15.33 = 0) not the actual values ?

Many thanks


--
Tobit Curteis
________________________________

Tobit Curteis Associates
36 Abbey Road
Cambridge
CB5 8HQ
UK

Tel: +44 (0)1223 501958
Fax: +44 (0)1223 304190
E-mail:
Web: www.tobit-curteis-associates.com