|
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!
|
|
|
|
|
Where "tots" is the cell having the total inheritance, and "recipients" is the range with the names:
=tots/rows(recipients)
(at least it worked with a quick and cheesy test(tm))
TTFN - Kent
|
|
|
|
|
Huh. Show how little I know about Excel.
|
|
|
|
|
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!
|
|
|
|
|
Cost Accounting is unique to each company. Don’t ask me why or how but we had to pay our software vendor a goodly sum of money to modify their cost accounting package to meet our needs. Then I found out that one $600 million company in our neighborhood in Silicon Valley was running its cost accounting on spreadsheets.
Don’t ask me how you can have anything other than Last-In-First-Out (LIFO), First-In-First-Out (FIFO), or Average Cost as the methods of valuing inventory but accountants manage to tell you that their company is uniquely different.
And I had enough on my plate as an IT Manager that I left it to the cost accountant and our software vendor to work out what should be done and I merely signed off on the contract and on the final invoice. The software vendor was sufficiently honest that we had no cost or schedule overruns.
The other area that you will find where spreadsheets are used is Sales Forecasting. After paying $50,000 to buy a basic package, we spent $200,000 modifying it and then threw it out. There is no pleasing salescritters.
|
|
|
|
|
OriginalGriff wrote: 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.
At one point I wrote an automation tool for generating reports from a spreadsheet like that - it was designed by a bookkeeper. It actually wasn't as bad as you might think, as the bookkeeper was good at her job, and it was very organized.
I wasn't about to move her to even Access, given her lack of tech background. She wasn't great with computers, excepting Excel and QuickBooks.
In another case, I stored invoice data in a flat file in CSV format for a cab company that didn't even have Office.
I believe in meeting people where they're at. It's *my* job to make it work with *them*, not to make them work around what I built. At least that's how I see it.
When I'm doing requirements gathering for people and situations like this, I have them walk me through their job.
Then I design something that works with the way they already operate, and streamlines it.
Real programmers use butterflies
|
|
|
|
|
Hey, I wrote my first few database engines using QuickBasic 4.5, on floppies.
If you think 'goto' is evil, try writing an Assembly program without JMP.
|
|
|
|
|
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.
modified 27-Jan-22 11:26am.
|
|
|
|
|
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
|
|
|
|
|
Now that's the sort of answer I can deal with. I like the original reply I got, but still haven't taken the time to figure out where I would use that equation.
|
|
|
|
|
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%.
|
|
|
|
|
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.
|
|
|
|
|
dandy72 wrote: increased by an equal amount?
That would depend on cause of death and amount of involvement by the survivors wouldn't it? That would make the equation even more complex. 
The less you need, the more you have.
Even a blind squirrel gets a nut...occasionally.
JaxCoder.com
|
|
|
|
|
Well, I mean, remove the others from contention first...
|
|
|
|
|
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.
|
|
|
|
|
Nice. I just might use that, or adapt mine to do something similar (I haven't yet looked at it...)
|
|
|
|
|
|
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.
|
|
|
|
|
I figured using a "fixed" percentage wasn't going to be solution as it needs to be changed when someone dies. I'll rework it with your answer in mind. Thanks!
|
|
|
|
|
Having business critical logic in spreadsheets should be (and maybe is?) considered an anti-pattern.
A big part of my job is actually replacing Excel files that specific people know how to use, but no one knows how they work.
When these specific people are somehow unable to do their job, a lot of work isn't done, isn't done correctly and/or isn't done fast enough.
It's a huge risk for companies, but unfortunately things often have to go bad before they start thinking about replacing Excel files 
I get it though, Excel can do a lot and costs a little.
It's a live-saver, especially for smaller organizations.
There's probably a graph somewhere with the usefulness vs. liability of an Excel spreadsheet compared to the size of the organization.
For small companies the usefulness far outweighs the liability, but the bigger the organization becomes the bigger the liability and the smaller the usefulness until the liability becomes greater (but of course they'll keep using it for another 20 years or so).
|
|
|
|
|
And it becomes the users hammer when their need is not met by their IT shop.
MS Access the same. Their project doesn't make the cut for time or money, and they have the skills.
And, Excel allows them to put text in what should be a number field. Those pesky databases with all the rules ...
|
|
|
|
|
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...
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
