ROUND function

[leopardhawk]

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

 

[Misca]

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

 

[leopardhawk]

@Misca Thank you! It works perfectly.

 

[leopardhawk]

@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]

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]

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

 

[Misca]

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]

@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.
=ROUND(your formula,2)