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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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

ADVERTISEMENT

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,144,326
Messages
5,723,725
Members
422,512
Latest member
MHau5

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