Rounding a number in function of another number

huorsa

Board Regular
Joined
Feb 15, 2002
Messages
101
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
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

Board Regular
Joined
Feb 15, 2002
Messages
101
Thanks Alriemer it works, I will think about your idea of using an extra cell.
 

huorsa

Board Regular
Joined
Feb 15, 2002
Messages
101
Yogi, what is the function of the cell A5 in the formula
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCD
142.14
25.02
347.16
4
547.16
6
7
85.01
9
1047.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
 

Forum statistics

Threads
1,181,643
Messages
5,931,186
Members
436,780
Latest member
doknights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top