# ROUND function

#### leopardhawk

##### Well-known Member
Hello forum friends, the formula below returns the result I need but, it isn't rounded to two decimal places. Instead, it gives me 1342.751667. I have been beating myself up over this trying to figure out what I am doing wrong. Any guidance is appreciated.

=ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),2)+(ROUND((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084))+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),2))/12

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Misca

##### Well-known Member
Put the "/12"-part inside the ROUND as well and you're done!

#### leopardhawk

##### Well-known Member
@Misca Thank you! It works perfectly.

#### leopardhawk

##### Well-known Member
@Misca Good morning, I am hoping that you can help again with this issue, I may have spoken too soon. I did as you suggested and moved the /12 inside the ROUND but the result is very different. Instead of 1342.75 (the correct result), I am getting 2794.55. Hoping you know why? Thanks!

#### AhoyNC

##### Well-known Member

Does this work?

=ROUND((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),2)+(ROUND((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084))+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084)/12,2)

#### leopardhawk

##### Well-known Member
@AhoyNC that's weird. When I copy your formula into a cell, it displays the formula and not the result.

#### Misca

##### Well-known Member

The weird result you're getting is likely caused by the order of operations (= multiplications and divisions take place before the additions and subtractions):

Try putting your original formula in brackets to make sure they're all added together before the division.

#### Joe4

@AhoyNC that's weird. When I copy your formula into a cell, it displays the formula and not the result.
If that happens, it sounds like your formula is being entered as "Text" and not as a formula.
Make sure the format of the cell is set to "General" or some "Number" option.
Then, select the cell, click F2, and hit enter.

#### leopardhawk

##### Well-known Member
@Joe4 The cells I am testing in are all set to "General". Another strange thing, if I highlight a cell and then click 'Paste' on the ribbon, the formula is displayed in the cell (as I indicated earlier). However, I notice that if I highlight a cell and paste the formula into the 'Input Bar' and hit 'Enter', I get an error (There is a problem with this formula.) I'm sure the answer is probably something simple, all I am trying to do is ROUND an already correct result. I appreciate everyone's help.

#### Joe4

It sounds like there is an error in your formula (often times, mismatched parentheses when the formulas get that long).

I would forgetting about rounding for a minute, and rewrite your whole formula without it, including the division by 12.
Then when you have it working correctly (no errors), apply the rounding at that point, i.e.

Replies
3
Views
78
Replies
26
Views
328
Replies
3
Views
158
Replies
7
Views
89
Replies
18
Views
243

1,129,439
Messages
5,636,296
Members
416,911
Latest member
jafornwalt

### 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.

### Which adblocker are you using?

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

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