ROUND function

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 12, 2009
Messages
1,689
Put the "/12"-part inside the ROUND as well and you're done!
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Oct 10, 2011
Messages
4,759
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@AhoyNC that's weird. When I copy your formula into a cell, it displays the formula and not the result.
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,689

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,912
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,912
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Watch MrExcel Video

Forum statistics

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