Morbid spreadsheet...
-
I'm no Excel wizard, and I've been presented with a spreadsheet with a problem that's been bothering me. I promise this is not some homework. Let's say there's a total amount of money (T) to be inherited, as part of a will, by N number of people. Different people are given a fixed percentage. Say there's 10 people named in the spreadsheet--they don't all get the same percentages, but the total allocated (as a percentage) has to add up to 100%. Bob is one of those people, and he's supposed to get 15% of T. But if Bob dies, his 15% share should go back to the pot (T). If we simply delete the row that defines Bob and his 15%, we'll end up with an "unallocated" 15% (for lack of a better term). How would you even set up a spreadsheet so if a row is deleted whenever someone dies, the percentages for the remaining recipients are automatically increased by an *equal* amount? Assume whoever is *using* the spreadsheet (not the one who *wrote* it) only has enough knowledge to fill in the value for T, and deleting rows. I'm starting to think Excel's built-in formulas aren't sufficient for this, and this is where you have to get into automating Excel. Blech!
-
Excel is a seriously good spreadsheet, and can easily be mismanaged to the point of terminal stupidity. An ex-boss had an Excel file that ran the whole company: stock control, human resource allocation, BOM, ordering, the whole nine yards. It started as a quick and dirty stock inventory ... and grew like topsy to the point where it took 20 minutes just to load ... and seemingly forever to update. But it worked. And there wasn't even VBA to help in those days, it was all cell formulae ... I did try to convince him to move to a DB, but then he dragged out a stack of Basic floppies and I changed my mind.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Excel is a seriously good spreadsheet, and can easily be mismanaged to the point of terminal stupidity. An ex-boss had an Excel file that ran the whole company: stock control, human resource allocation, BOM, ordering, the whole nine yards. It started as a quick and dirty stock inventory ... and grew like topsy to the point where it took 20 minutes just to load ... and seemingly forever to update. But it worked. And there wasn't even VBA to help in those days, it was all cell formulae ... I did try to convince him to move to a DB, but then he dragged out a stack of Basic floppies and I changed my mind.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
Excel is a seriously good spreadsheet, and can easily be mismanaged to the point of terminal stupidity.
I keep hearing that Excel's probably the program that has been the most twisted and abused to do things it was never intended to do. I have no problem believing that. I just wanted to make sure I didn't get into that rabbit hole when I asked my question.
-
columns: name, gets share (1 or 0), base share (percentage), intermediate value (percentage, needs better name) actual share columns A, B, and C are user entered. column D is a formula = B2*C2 column E is a formula = D2/SUM(C2:C#) where # is the last row with data in it.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
Don't think of the 15% as a percentage, but a proportion of the total. So to calculate how much each person gets, add up all the proportions, to give a total 'proportion'. Then for each person, the amount they get is (their 'proportion' * total inheritance)/(total 'proportion'). Then deleting a person from the spreadsheet when they die, will increase the others inheritance according to their proportion.
-
Why delete Bob's row if he dies? Why not just mark him as deceased and change his percentage to zero? Wouldn't that cause the percentages to automatically recalculate?
If you think 'goto' is evil, try writing an Assembly program without JMP.
That's the problem with the spreadsheet as it exists right now, percentages are "fixed", but need to be recalculated when someone dies. For simplicity's sake - assuming Bob (worth 15%) dies, and that still leaves 15 people alive on the list, then each one of them gets an additional 1% (15% / 15 people). If there's 30 people still alive, each gets an additional 0.5%. I'm not sure if I had conveyed that properly in my initial post...
-
Here's one way: deathInheritance.xlsx (9.5 KB). If you drag the middle rows out, or delete them, you can see how things change. You might have to readadjust the calculation to distribute differently.
Our Forgotten Astronomy | Object Oriented Programming with C++
-
dandy72 wrote:
increased by an equal amount
That doesn't sound right.
What I think I should've said here is that if Bob (worth 15%) dies, and there's 15 people left on the list, Bob's 15% should be redistributed equally among those 15 people - so each person gets an extra 1%. If there's 30 people, they all get an extra 0.5%.
-
What I think I should've said here is that if Bob (worth 15%) dies, and there's 15 people left on the list, Bob's 15% should be redistributed equally among those 15 people - so each person gets an extra 1%. If there's 30 people, they all get an extra 0.5%.
That's what I thought, but it seems to me that if Alice is due a 50% share of T, that she should get 50% of Bob's 15% share of T.
-
I'm no Excel wizard, and I've been presented with a spreadsheet with a problem that's been bothering me. I promise this is not some homework. Let's say there's a total amount of money (T) to be inherited, as part of a will, by N number of people. Different people are given a fixed percentage. Say there's 10 people named in the spreadsheet--they don't all get the same percentages, but the total allocated (as a percentage) has to add up to 100%. Bob is one of those people, and he's supposed to get 15% of T. But if Bob dies, his 15% share should go back to the pot (T). If we simply delete the row that defines Bob and his 15%, we'll end up with an "unallocated" 15% (for lack of a better term). How would you even set up a spreadsheet so if a row is deleted whenever someone dies, the percentages for the remaining recipients are automatically increased by an *equal* amount? Assume whoever is *using* the spreadsheet (not the one who *wrote* it) only has enough knowledge to fill in the value for T, and deleting rows. I'm starting to think Excel's built-in formulas aren't sufficient for this, and this is where you have to get into automating Excel. Blech!
I may be totally wrong, but my initial logic says that if you add up the "percentages" column and then give each person T x "MyPercentage"/"TotalPercentages" it will keep the proportions if one or more lines are deleted/cleared, or even added.
So old that I did my first coding in octal via switches on a DEC PDP 8
