Excel - Rounding of Figures.

_stimb_

I am working with Excel spreadsheets which concern figures relating to insurance premiums and commissions. The commissions offered by the company possess three decimal places (eg. 18.775%/ 15.000%.

However, for reasons that would be overly lengthy to explain, I need the spreadsheet to calculate these figures using two decimal places.

EXAMPLE: Net Premium €2770.50; Commission Rate 15.000%; Commission 415.575 >> round to 415.58; Net Premium - Commission (2770.50-415.58) = €2354.92.

The above is how I require Excel to do the math. To this end, I have imbued the cells displaying the commission value (not percentage) and Net Premium with formulas allowing them to display at two decimal places using the formula "ROUNDUP(A4,2)"

These do display at two decimal places, but appear to still factor in the third decimal place in the final calculations.

EXAMPLE: Net Premium €2770.50; Commission Rate 15.000%; Commission 415.575 >> round to 415.58 (DISPLAY); Net Premium - Commission (2770.50-415.575) = €2354.925 *BEING ROUNDED UP to €2354.93 and ultimately creating a discrepancy to the value of a single cent on all payment receipts.

So what I essentially need to know (I think) is how to get the formulas for that final cell which displays the net premium to deduct the commission value relevant to the TWO DECIMAL PLACES as it *displays* and not the three decimal places which actually exist.

Bare bones: where there is (2770.50-415.575) I need (2770.50-415.58)

Any help on the matter would be greatly appreciated.

stimb

sullivlo

Can you highlight the cells, right click, format, and set decimal places to 2?

Yer Aul One

=ROUNDUP(Cell Ref, Number of Decimal Places)

_stimb_

I had that in place alright, that seems to be what's causing them to display as the desired figures, but not actually changing them within the formulas.

_stimb_

Yer Aul One wrote: »

=ROUNDUP(Cell Ref, Number of Decimal Places)

Is that not what I had in place with ROUNDUP(A4,2) ? What should the cell reference be, the actual cell that contains the figure in question? Seems obvious, but when I reference that cell in the formula Excel gives a warning that it may corrupt the result, and then just displays the commission as 0.00

Yer Aul One

Create new columns for the raw data you want rounded up, use the roundup function in these new columns and reference the raw cell values

coffee_cake

I just checked it and roundup is working for me also
stuck 415.575 into cell H85
=ROUNDUP(H85,2) returns 415.580 in cell H86
=2770.5-H86 returns 2354.920

_stimb_

bluewolf wrote: »

I just checked it and roundup is working for me also
stuck 415.575 into cell H85
=ROUNDUP(H85,2) returns 415.580 in cell H86
=2770.5-H86 returns 2354.920

Interesting that your rounded down figure occupies a separate cell, perhaps this is the right track. I feel it would be a lot simpler if boards would allow to post the screen grab I prepared.

The way my spreadsheet is set up moves horizontally.

A3 - date. B3 - Client name. C3 - Policy Type. D3 - Policy Number. E3 - Transaction Number. F3- Transaction Type (All irrelevant, but merely to help with picture) G3 - Gross Premium. H3- Commmission Percentage (15.000%). I3 -Government Levy (irrelevant, always at zero). J3 - Commission, formula here is "=(-G3*H3)". K3 - Net Premium, formula is "=G3 + J3"


Could you possibly give me a fomulaic recommendation based on this?

Yer Aul One

J3 Cell = ((-RoundUP(G3,2)*(RoundUp(H3,2))

K3 Cell = (RoundUP(G3,2)+(J3)

Victor

_stimb_ wrote: »

EXAMPLE: Net Premium €2770.50; Commission Rate 15.000%; Commission 415.575 >> round to 415.58 (DISPLAY); Net Premium - Commission (2770.50-415.575) = €2354.925 *BEING ROUNDED UP to €2354.93 and ultimately creating a discrepancy to the value of a single cent on all payment receipts.

Mathematically, both 415.575 and 2354.925 are rounded up when rounded to the nearest whole cent.

In practical terms, you need to decide whether the insurance company or the broker takes the hit for the half cent.

_stimb_

Well, thanks kindly for everyone's help, all contributions opened paths which ultimately allowed us to figure out a solution that works. Ultimately opting for ROUND over ROUNDUP seems to have been the catalyst.

Changing the formula for the commission value (J3) to "=ROUND(-G3*H3,2) has provided exactly the figures we were hoping to achieve.

stimb