|
I've created a spreadsheet for my job to advise people on planning for when to file for their Social Security benefits, that takes their DOB, their chosen month to retire, and their PIA (Primary Insurance Amount- basically SSA's base calculation for "what you're gonna get", which gets adjusted for age), and gives them what their benefit would be. It also has a table that shows what their benefit would be for each month from filing at their first eligibility at age 62 all the way to the maximum benefit at age 70. So essentially, it displays (9x12) 108 incremental amounts. I'd like for the whole office to use it, since we don't have a nice presentable handout to give folks with this information (partly by design- senior management wants us to push everyone to online services). Since using macro-enabled worksheets give security popups and I don't trust their computer skills enough not to panic, I'm trying to do it all with formulas instead of macros. So that means a bunch of nested IF statements. It doesn't help that they have a bunch of hosed up technicalities that affect your calculation; for example, SSA considers you to have attained your age the day before your birthday. So if I'm born on April 1st, 2000, I'm actually going to turn 20 in SSA's eyes on March 31 of next year. This is important because you can file for retirement benefits the first month that you are 62 in the entire month. So b-day on April 1 or 2, 2000? You can retire April 2062. Born April 3, 2000? You get to wait until May 2062 for your eligibility to start. Another wrinkle is your full retirement age (the one they're stepping out gradually from 66 now to 67 in a few years). Your full retirement age is your birth month, so in the above example, all 3 of those options would be their full retirement age in April 2067. Except, oh wait. You attain your age the day before your birthday. So a birthday April 1, 2000 would mean you reach your full retirement age in March 2067. So you have a bunch of different technicalities that change the calculations, which are all actually pretty simple. But to cover all the possibilities, I ended up with 4 different possible calculations (own benefit born on the 1st/not born on the 1st, and spousal born on 1st/not on 1st). Each of those has the full table of 21 different customer classes with 108 months of calculations. So I'm getting drat near 10,000 dependent calculations every time a new DOB is entered, and while my test spreadsheet seems to run fine and stable, it's not very tidy on the calculation end. Plus the actual display sheet has in those 108 months a nightmare of nested IFs (If customer type A, this cell, if customer type B, this cell, etc all the way to customer type U. So I'd like to take another pass at this. Is there a real worry of things getting twitchy or slow with these many numbers running? Or is this essentially irrelevant?
|
#
¿
Dec 1, 2019 07:11
|
|
|
|
| # ¿ May 12, 2024 10:36 |
|
|
The things that vary between the 21 different customer classes are: 1. full retirement age (FRA- stepped out from 65 to 67 in 2 month increments) 2. delayed retirement credits (like someone born in x year gets 7/12ths of a % additional credit each month they wait over FRA, but someone born the next year gets 15/24ths of a %). I don't see a way of doing it without the customer classes, but I guess I could take all the age 62 to age 70 tables and just shrink them down to one table with a whole shitload of nested IFs. As it is, it makes all the calculations for the numbers given and then just takes the appropriate ones. This would have to be quicker, right? Like, the cell for retirement amount at age 64 and 6 months would be this calculation if customer class A, this calculation if customer class B, etc.
|
|
#
¿
Dec 1, 2019 16:54
|
|
|
mystes posted:I don't know the exact details of the calculation, and you might need to have a whole table for the credits depending on how it works, but you still haven't described anything that suggests that you need to calculate the values for each month more than once in your spreadsheet. Yeah, that does seem to be the big change I need to make. Thanks for the help. Zorak of Michigan posted:Newer Excel versions have the SWITCH function, which can help tidy up nested IF statements. Hmmmm gonna look into this, thank you
|
|
#
¿
Dec 1, 2019 18:15
|
|
|
Fun fact I discovered today when trying to get SWITCH and IFS to work today; we have Excel 2016 at work. You get IFS and SWITCH if you got Excel 2016 with Office 365. If you got just Office 2016 with a license, you don't get those and other functions! So I did take your advice and shrink it down to one table. I would have loved to killed the nested IFs, but I at least figured out a way separate all the IFs on a scratchpad sheet and concatenate them, making it easier to error-check before assembling Iffenstein's Monster. When or If I submit this to our completely worthless automations person (the one time when I asked her an Excel question, she emphatically told me she had an English degree and cut the conversation short), I can't wait to hear someone ask why I did it the way I did.
|
|
#
¿
Dec 3, 2019 06:31
|
|
|
Single click to do it once, double click to keep doing to every cell you touch until you hit Escape to stop it. Be very careful with the double click. You can put some weird poo poo all over your spreadsheet if you're careless.
|
|
#
¿
Mar 23, 2020 05:50
|
|
|
I'm decent on screwing around with formulas but with VBA I'm a complete Google-cut-paste basket case. A program at work exports raw data to Excel to Book1.xlsx on your desktop I have a spreadsheet that can take all that data and format it how I want; I'm trying to make one dummy-proof button (seriously, it's a big button) that will copy raw data from that day's run (as shown in Book1), paste it into my spreadsheet where everything gets table-ized, automated, and a nice results tab shows up. I can't get past step one. I get an out of range error on the first line: Workbooks("Book1.xlsx").Activate I fully admit that I am screwing around with things beyond my understanding, but I feel like I can jury rig this with mostly recorded macro steps if I just get past this.
|
|
#
¿
Nov 21, 2023 03:59
|
|
|
Wandering Orange posted:Is the Book1 file already open when you run it and get that error? Yes. It isn't saved or anything, so not sure if the file extension might be the issue (it wants to save as an .xlsx when I try)
|
|
#
¿
Nov 21, 2023 05:09
|
|
|
I was already leery of using a macro-enabled workbook for this purpose (our intranet pops up a warning when you open one, and some people will freak out), but having a macro that saves something is a complete no-go. To be clear, I can do move all this data around myself (I've used the spreadsheet for years), but I'm trying to dummy-proof and one-step it (as much as practicable) for wider use. If I can't, I'd be ok with just a sarcastically big red arrow that says "post data here, using destination formatting"
|
|
#
¿
Nov 21, 2023 05:53
|
|
|
Similar question. I have to copy info from a program and paste it into an Excel tab every day, to try and keep track of a specific type of transfer we have a quota for. (Our MI sucks) I've got a different tab with enough formulas that pull from the tab with raw data to get the basic information we need (# of transfers, and who has fulfilled their quota/who hasn't). The table always ends up with some garbage in it, but I can live with that. Since I'm trying to expand use of this sheet to people who are more scared of Excel (and just polish it up), and I'm weaksauce on the VBA side, would the best option be an input form, where they can hit a button to paste clipboard contents to cell A1 of the (hidden) cell for the raw data? I've just been copying and pasting with destination formatting so far. Follow-up; is there a way in the macro to then have the data clipped at a specific point before and after the info I need? When I copy from the website, there's a few lines of junk on top and on bottom, and I just need the stuff that would, in any sane program, be exportable to text/excel. GD_American fucked around with this message at 04:14 on Jan 24, 2024 |
|
#
¿
Jan 24, 2024 04:12
|
|
|
|
| # ¿ May 12, 2024 10:36 |
|
|
I love COUNTIFS, but I wish they could use ranges of different sizes.
|
|
#
¿
Feb 3, 2024 15:50
|
|