First time poster here - I'm struggling to explain what I'm trying to do so excuse my fumbling. 
I'm trying to create a spreedsheet to keep track of upcoming bills and work out how much I need to be puting aside each week to cover them when they hit.
The bills are weekly, some monthly, some quarterly and some yearly - they could be any time.
So the goal is to average each one out on a weekly basis starting from the previous bill, figure out each bills weekly amount, add each bills portion up to give me a total of what I need to be saving week by week.
Ive created a dummy sheet (see below) and it works, but adding new data is very time consuming to do. Each value added means re setting the range of each Average formula for each cell and I'm hoping there is a way to automate the process.
Example- For biller name "123" there is an expected bill for 1600 in week 48 (cell J2). Given this sheet starts in week 41 (C2), that means the 1600 is averaged out to be 200 per week. The value in the grey rows is the actual bill amount, while the values in the white rows is the weekly average for the bill above it.
I'm hoping there is a way to automatically finds the average value of the cells in the row above it, ranging from the last zero directly above (or to the left), until it gets to the next value grater than zero directly above or to its right.
I'm trying to create a spreedsheet to keep track of upcoming bills and work out how much I need to be puting aside each week to cover them when they hit.
The bills are weekly, some monthly, some quarterly and some yearly - they could be any time.
So the goal is to average each one out on a weekly basis starting from the previous bill, figure out each bills weekly amount, add each bills portion up to give me a total of what I need to be saving week by week.
Ive created a dummy sheet (see below) and it works, but adding new data is very time consuming to do. Each value added means re setting the range of each Average formula for each cell and I'm hoping there is a way to automate the process.
Example- For biller name "123" there is an expected bill for 1600 in week 48 (cell J2). Given this sheet starts in week 41 (C2), that means the 1600 is averaged out to be 200 per week. The value in the grey rows is the actual bill amount, while the values in the white rows is the weekly average for the bill above it.
I'm hoping there is a way to automatically finds the average value of the cells in the row above it, ranging from the last zero directly above (or to the left), until it gets to the next value grater than zero directly above or to its right.
| Fixed Expenses Planner.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | W 41 | W 42 | W 43 | W 44 | W 45 | W 46 | W 47 | W 48 | W 49 | W 50 | W 51 | W 52 | ||||
| 2 | 123 | 1600 | ||||||||||||||
| 3 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | ||||||||
| 4 | ABC | 100 | 100 | 100 | 100 | 100 | 100 | |||||||||
| 5 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | ||||
| 6 | qwerty | 800 | ||||||||||||||
| 7 | 160 | 160 | 160 | 160 | 160 | |||||||||||
| 8 | asdfg | |||||||||||||||
| 9 | ||||||||||||||||
| 10 | lmnop | |||||||||||||||
| 11 | ||||||||||||||||
| 12 | amamama | |||||||||||||||
| 13 | ||||||||||||||||
| 14 | ghgiejfv | |||||||||||||||
| 15 | ||||||||||||||||
| 16 | 826342791 | |||||||||||||||
| 17 | ||||||||||||||||
| 18 | hebci7ft72f | |||||||||||||||
| 19 | ||||||||||||||||
| 20 | saadcsdcsvbnm | |||||||||||||||
| 21 | ||||||||||||||||
| 22 | Weekly total | $ 3,000.00 | 100 | 100 | 800 | 100 | 1700 | 100 | 100 | |||||||
| 23 | Total per week | $ 3,000.00 | $ 410.00 | $ 410.00 | $ 410.00 | $ 410.00 | $ 410.00 | $ 250.00 | $ 250.00 | $ 250.00 | $ 50.00 | $ 50.00 | $ 50.00 | $ 50.00 | ||
Sheet1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3 | C3 | =AVERAGE(C2:J2) |
| D3 | D3 | =AVERAGE(C2:J2) |
| E3 | E3 | =AVERAGE(C2:J2) |
| F3 | F3 | =AVERAGE(C2:J2) |
| G3 | G3 | =AVERAGE(C2:J2) |
| H3 | H3 | =AVERAGE(C2:J2) |
| I3 | I3 | =AVERAGE(C2:J2) |
| J3 | J3 | =AVERAGE(C2:J2) |
| K3,M3,C5,E5,G5,I5,K5,M5 | K3 | =AVERAGE(K2:L2) |
| L3,N3,D5,F5,H5,J5,L5,N5 | L3 | =AVERAGE(K2:L2) |
| C7 | C7 | =AVERAGE(C6:G6) |
| D7 | D7 | =AVERAGE(C6:G6) |
| E7 | E7 | =AVERAGE(C6:G6) |
| F7 | F7 | =AVERAGE(C6:G6) |
| G7 | G7 | =AVERAGE(C6:G6) |
| H7 | H7 | =AVERAGE(H6:N6) |
| I7 | I7 | =AVERAGE(H6:N6) |
| J7 | J7 | =AVERAGE(H6:N6) |
| K7 | K7 | =AVERAGE(H6:N6) |
| L7 | L7 | =AVERAGE(H6:N6) |
| M7 | M7 | =AVERAGE(H6:N6) |
| N7 | N7 | =AVERAGE(H6:N6) |
| C9,C21,C19,C17,C15,C13,C11 | C9 | =AVERAGE(C8:N8) |
| D9,D21,D19,D17,D15,D13,D11 | D9 | =AVERAGE(C8:N8) |
| E9,E21,E19,E17,E15,E13,E11 | E9 | =AVERAGE(C8:N8) |
| F9,F21,F19,F17,F15,F13,F11 | F9 | =AVERAGE(C8:N8) |
| G9,G21,G19,G17,G15,G13,G11 | G9 | =AVERAGE(C8:N8) |
| H9,H21,H19,H17,H15,H13,H11 | H9 | =AVERAGE(C8:N8) |
| I9,I21,I19,I17,I15,I13,I11 | I9 | =AVERAGE(C8:N8) |
| J9,J21,J19,J17,J15,J13,J11 | J9 | =AVERAGE(C8:N8) |
| K9,K21,K19,K17,K15,K13,K11 | K9 | =AVERAGE(C8:N8) |
| L9,L21,L19,L17,L15,L13,L11 | L9 | =AVERAGE(C8:N8) |
| M9,M21,M19,M17,M15,M13,M11 | M9 | =AVERAGE(C8:N8) |
| N9,N21,N19,N17,N15,N13,N11 | N9 | =AVERAGE(C8:N8) |
| B22:B23 | B22 | =SUM(C22:N22) |
| C22:N23 | C22 | =SUM(C2,C4,C6,C8,C10,C12,C14,C16,C18,C20) |
.
