ROUND function

leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
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
 
Sort by date Sort by votes

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
leopardhawk said:
@Joe4 thanks, I will try that.
Click to expand...
You are welcome.

I find that if you have something big and complex that doesn't work, one good way to fix it is to start over from scratch, and rewrite it starting just with the first part. When you get that working, add the second, and if that works, add the third, etc.
 
Upvote 0
@Joe4 @Misca @AhoyNC Joe, I did as you suggested and re-wrote the formula from scratch, making sure each section worked as I went along. I also got rid of ALL of the ROUND(s) in my original formula. Now everything is working as anticipated. Of course, I'll never really know exactly what was causing the problems but as you said, most likely a misplaced parenthesis or something along those lines. Thanks to all of you that stepped in to try and help me, I am super relieved that this one is now working! Cheers!

Posting the formula below since it's working now (I had to split the formula into two because of the 8192 character limit!!! I know, it's likely some folks on here will laugh and I'm sure there's probably a simpler way to do it but my Excel skills are very limited. :p

PART 1
=CONCATENATE("Let's assume "&IF(personal_info!C9=0,"Name",personal_info!C9)&" starts "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" CPP at age 65 but continues to work and make CPP contributions. Using the "&YEAR(TODAY())&" maximum retirement benefit of "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&" per month and"," YMPE of "&TEXT(INDEX(CPP_OAS[Thresholds],MATCH("YMPE",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&", we can calculate "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" annual PRB at age 66 at "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&"."," If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" continues to work past age 66, "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" PRB from the previous year is added to the current year's PRB for "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" lifetime and like the CPP, the PRB is indexed to inflation! However, the good news doesn't end there. From age 65 to 70, an additional 8.4% per year (.7% per month) is added to your PRB! So, assuming "&IF(personal_info!C9=0,"Name",personal_info!C9)&" keeps working until "&IF(personal_info!F9="M","he's",IF(personal_info!F9="F","she's","they're"))&" 70...

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 68 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*0.025*12*1.084,"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 69 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")++TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 70 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&".")

PART 2
=CONCATENATE("If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" continues to work after reaching age 70, "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" will no longer contribute to CPP but now "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" will receive an additional $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&" (indexed to inflation) every year for the rest of "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" life! "&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s monthly CPP retirement benefit is now at $"&ROUND(TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")+(TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00"))/12,2)&".", " If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" lives to 87 years of age, "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" total accumulated PRB (taking inflation into account) would be "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5*3+262.23,"$#,##0.00")&". This is certainly much more than the "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5,"$#,##0.00")&" "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" would have received by not contributing.", " Even if "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" invested the "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5,"$#,##0.00")&" "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" (which we all know most people won't do), "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" would need an unlikely 6% return (compounded annually) to grow it to "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5*3+262.23,"$#,##0.00")&" in the same amount of time."," Careful consideration should be given to how much you would gain by continuing to contribute. And remember, your employer is also matching your contributions on your behalf.")


ReplyForward
 
Upvote 0
You must log in or register to reply here.

Similar threads

leopardhawk
  • Solved
Trying to use division within INDEX/MATCH formula
Replies
3
Views
1K
AhoyNC
A
leopardhawk
  • Solved
Trying to SUM two amounts in a formula that are calculated in the same formula
2 3
Replies
26
Views
1K
Candyman8019
C
leopardhawk
  • Solved
Need advice on division of two INDEX/MATCH amounts
Replies
3
Views
644
leopardhawk
leopardhawk
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

Share this page

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
Back
Top