Rounding questions: (Formula and Code) ?

[Stuart]

I have two types of data on which to perform multiplication:

Type 1:
C D E
1.175 * 975 = 1145.625
where 1145.625 rounds to 1145.63 and
if 1145.626 then round to 1145.63, but
if 1145.624 then round to 1145.62

Type 2:
44.44 * 12.5% = 5.555
where 5.555 rounds to 5.56 and if
5.556 then round to 5.56, but if
5.554 then round to 5.55

Each calculated decimal place is included in the rounding,
thus 1.23456789 would round to 1.24
but 1.23356789 would round to 1.23

I can differentiate between the 2 types of data.

How can I use code to get the values in col E, please?

Secondly, a SUM formula currently totals col E. If I
permit a user to enter a value in col E (say in a row
where code had not operated), then that new value is
reflected in the new Summed cell. Can I ensure that
the Summed cell is also rounding along the same lines?

It does not matter that the user's entry might exceed 2
decimal places, just that the summed value is 'corrected'.

Any help would be much appreciated. Thanks.

Regards.

 

[Frank Kabel]

Hi Stuart
why not use
=ROUND(C1*D1,2)
in cell E1

For the second question if the user enteres values manually try the
following array formula
=SUM(ROUND(E1:E100,2))
to round each entry individually

also have a look at
http://www.cpearson.com/excel/rounding.htm

 

[Stuart]

Many thanks.

Regards.

Hi Stuart
why not use
=ROUND(C1*D1,2)
in cell E1

For the second question if the user enteres values manually try the
following array formula
=SUM(ROUND(E1:E100,2))
to round each entry individually

also have a look at
http://www.cpearson.com/excel/rounding.htm