Formula to recalculate values based on one value becoming static?

This was a neat little project to learn how to write Functions.

I created two User Defined Functions: one to Sum non-formula cells, and another to Count the number of formula cells.

Then I used a formula: (TotalBudget-NotFormulaSum(range))/IsFormula(range)

The result is that the running dispursement updates dynamically as hard-coded figures are entered manually over the existing formulas.


Here's the quick and easy version of how to make this work for you:

In Excel, hit Alt+F11

Go to Insert->Module

Paste the code below:

Please Login or Register  to view this content.

Now close that window and use the new Functions as needed. Change "range" in my formula to whatever cells you need them to be.

In the attached example, I made A2:A13 months, and B2:B13 the expenditures, with B14 being the $400,000.

B2: =($B$14-NotFormulaSum($B$2:$B$13))/isformula($B$2:$B$13)

and copied down

Awesome. I learned so much doing it too!

Now I know that if I really want to annoy my boss, I can add:

Please Login or Register  to view this content.

It works exactly the same as SUM, but instead it's called NUMBER_SMASH. Because I had too much sugar.

The possibilities..