Rounding a number in function of another number

[huorsa]

Let's say we I have in A1 42.14,in A2 5.02 and in A3 A1+A2, A3 will be equal to 47.16. I want to round the second decimal of A3 to 0 or 5 the closest one changing A2.

In the example A3 shoulb be 47.15 so I want that A2 automatically changes to 5.01

Any ideas????

Thanks.

 

[Alriemer]

Huorsa,

In your example,
=MROUND((A1+A2),0.05)-A1
will give the corrected value for A2 (5.01). But how to have it overwrite A2 is mor than I can do, needs vb - is it necessary to replace the data in A2? If you have some extra rows you could keep the data originally entered as well as modifying it.

Alriemer

 

[huorsa]

Thanks Alriemer it works, I will think about your idea of using an extra cell.

 

[Yogi Anand]

Hi huorsa:

Try ...

=A2+INT(A5)+FLOOR(MOD(A5,1),0.05)-A3

Regards!

Yogi

 

[huorsa]

Yogi, what is the function of the cell A5 in the formula

 

[Yogi Anand]

On 2002-10-09 22:19, huorsa wrote:
Yogi, what is the function of the cell A5 in the formula

Hi Huorsa:

cell A5 houses ... =A1+A2

Also look at the following worksheet simulation

y021009h1.xls
	A	B	C	D
1	42.14
2	5.02
3	47.16
4
5	47.16
6
7
8	5.01
9
10	47.15
Sheet7

</SPAN>
Regards!

Yogi

Edit: I am sorry, I had missed showing what was in A5; same as in A3
This message was edited by Yogi Anand on 2002-10-09 22:30

 

[huorsa]

Thanks Yogi I got it.