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:

Function IsFormula(rng As Range)

Dim cells As Range, x As Long
For Each cells In rng
If cells.HasFormula Then x = x + 1
Next
IsFormula = x
End Function

Function NotFormulaSum(rng As Range)
Dim cells As Range, x As Long
For Each cells In rng
If Not cells.HasFormula Then x = cells + x
Next
NotFormulaSum = x
End Function

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:

Function NUMBER_SMASH(rng As Range)

Dim cells As Range, x As Double

For Each cells In rng
    x = cells + x
Next cells

NUMBERSMASH = x

End Function

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

The possibilities..