Budgeting/Allocated Spending Plan missing value formulas

fevzay

New Member
Joined
Nov 23, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
    Budget.PNG
    9.4 KB · Views: 1

Some videos you may like

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
Joined
Nov 23, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top