# Budgeting/Allocated Spending Plan missing value formulas

#### fevzay

##### New Member
Hi everyone,

I have been using an allocated spending plan for years now, and it's worked great but I'm wanting it to be more interactive and receptive to missing values. How I use the spreadsheet is to basically allocate an entire paycheck from top to bottom. I have multiple columns for each incoming paycheck, so sometimes items move from one column (paycheck/spending column).

Currently, when I move a line item (e.g. a bill that will move to a different spending column), I have to manually adjust the formula in column C (remaining \$ to allocate) to link to the next available balance above (it may be directly above, or several lines above).

What I'd like to have happen is for 1) Column C (remaining \$) to populate only when an entry is present in the corresponding Column B (\$ spent), and 2) for the Column C amount to be able to identify the next Column C amount above. This may be the line directly above, or several lines above. Currently, I have to type into a cell, such as cell C35, "=C34-B35". The main issue I have is that my line items move all the time, and I'm having to constantly adjust them as well as having the risk of making a mistake.

I have played around with IF statements, and can have the Column C amount calculate only when an amount is in Column B (and stay blank when no column B entry is present). BUT....I am having an issue trying to nest logics which tell Column C to look for the next present value (above).

Lastly, I have some headers for different spending categories which will never have an amount in them. Will I need to carry over the formulas inside these cells as well?

#### Attachments

• Budget.PNG
9.4 KB · Views: 1

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### fevzay

##### New Member
Currently, I have to type into a cell, such as cell C35, "=C34-B35".

A key issue is that the C34 referenced above is not always going to be C34. It could be C33, C32, C31, C30, etc. I need a way to ignore missing values, and "look" up for the next value in column C.

Replies
2
Views
32
Replies
2
Views
62
Replies
18
Views
182
Replies
0
Views
48
Replies
1
Views
33

1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back