Author Topic: Spreadsheet Problem  (Read 405 times)

saijoe

  • 5 O'Clock Shadow
  • *
  • Posts: 93
Spreadsheet Problem
« on: August 08, 2018, 06:33:43 PM »
I normally consider myself pretty good with a spreadsheet, but I am having a mental block with what I'm trying to do.  We have a single Ally Savings Account that internally, we have subdivided into categories:

Ally Total: $23,987.00

Goals
Emergency Fund: $18,000.00
Property Taxes: $9,000.00
Travel: $5,000.00
Transportation: $15,000
Total: $47,000

The categories are in prioritized order.  So, the Emergency Fund is 100% funded.  Property Taxes are 66% funded. Travel and Transportation are both 0% funded.  As I fill up the Ally bucket, I want to show visually what percentage these categories are funded.  I picture a measuring cup with an E-Fund line, property tax line, etc. 

Anybody that could help, I'd certainly appreciate it.  Also, I am doing this in Google Sheets.  Thanks.   

chuckster

  • 5 O'Clock Shadow
  • *
  • Posts: 18
Re: Spreadsheet Problem
« Reply #1 on: August 09, 2018, 12:51:55 AM »
So, you want to type in the total you have in Ally, then have it calculate down the list, right?

Let's say the $23,987 is in cell C1

In A2 you have "E Fund".  In B2, you have "$18,000". In C2, type: =IF(C1>B2, B2, C1).  This will compare your savings account total to your first goal. If it's more than your goal, it'll enter your goal amount. If it's less, it'll use up your whole savings account and put it in C2.

In D2, type: =C2/B2*100 to get your percentage.

In A3 you type "Prop Taxes". In B3, you type "9000". Now, the tricky part. In C3 type: =IF(C1>B2, IF(C1-B2>B3, B3, C1-B2),0).  This will make sure there's money left over from the step above. If not, it'll enter 0. If there is money left over, it'll put how much is left over until you meet the goal, and then, it'll just put in the goal. In D3, type: =C3/B3*100 (just copy/paste from the cell above) to get your percentage.

Now, in A4 type "Travel", in B4 put "5000". In C4, you have to put the trickier bit: =IF(C1>SUM(B2:B3), IF(C1-SUM(B2:B3)>B4, B4, C1-SUM(B2:B3)),0). This checks to see if there's any money left over after taking care of the previous two lines. If there is, it puts in either how much is left, or, the goal if there's still more. Copy the percentage formula to D4.

From now on it's a little less tricky because we just have to adjust the parts above. In A5 type "Transpo", in B5 type "15000", and in C5: =IF(C1>SUM(B2:B4), IF(C1-SUM(B2:B4)>B5, B5, C1-SUM(B2:B4)),0). Basically, compare the savings account to the sum of the goals above, and let us know what's left over, until the transpo goal is reached. Copy your percentage formula into D5.


From now on, just update the total in your ally account in cell C1 and it'll figure out the rest automagically.


If you ever want to add more lines, modify the formula in the new C cell to SUM all the B's above it and compare it to the B on the new line. Just follow the pattern in cells C4 and C5 and extend as far down as you need.
« Last Edit: August 09, 2018, 12:59:25 AM by chuckster »

MDM

  • Walrus Stache
  • *******
  • Posts: 8273
Re: Spreadsheet Problem
« Reply #2 on: August 09, 2018, 01:52:08 AM »
One other way (of probably many more):

Ally Total23987
E-Fund18000=MIN($B$1,B2)=C2/B2
Prop Tax9000=MIN(B3,$B$1-SUM($C$2:C2))=C3/B3
Travel5000=MIN(B4,$B$1-SUM($C$2:C3))=C4/B4
Transp.15000=MIN(B5,$B$1-SUM($C$2:C4))=C5/B5
Extra:=MIN(B6,$B$1-SUM($C$2:C5))

Copying that table and pasting into cell A1 of a blank Excel sheet works.  Can't vouch for Sheets.

saijoe

  • 5 O'Clock Shadow
  • *
  • Posts: 93
Re: Spreadsheet Problem
« Reply #3 on: August 09, 2018, 07:46:56 AM »
Thanks all... I'll give it a try.  This is a forum full of smart, talented folks.