=SUM(range)/2
This formula could have saved me from a $1,000,000 mistake...
Ever have multiple sums & subtotals in your model? Like this:
cost 1
cost 2
cost 3
------------
subtotal 1
cost 4
cost 5
cost 6
------------
subtotal 2
________
Grand Total =SUM(subtotal 1, subtotal 2)
I encounter it a lot in a Headcount schedule, for example:
salary 1
salary 2
salary 3
------------
total salary
benefits 1
benefits 2
benefits 3
------------
total benefits
bonus 1
bonus 2
bonus 3
------------
total bonus
________
Grand Total Wages =SUM(total salary, total benefits, total bonus)
**********************
One time, I was helping a client build a headcount schedule.
We added "bonus expense" at the last minute.
And I FORGOT to update my "Grand Total" SUM formula at the bottom.
(basically, I left out the "total bonus" piece)
Which meant there was a $1,000,000 cost MISSING from our budget.
WHOOPS.
Fortunately, I caught it "soonish enough," (sort of...)
told the client, and thankfully didn't get fired.
(and sprinted to Google for a future solution)
**********************
You know what could have saved me?
๐ข =SUM(range)/2
Specifically โ right below my "Grand Total" line.
So, more accurately:
๐ข =SUM(my entire headcount schedule)/2 ๐ข๐๐ฃ๐ช๐จ the Grand Total
(โ should always equal $0)
**********************
This Excel 101 formula ๐๐จ๐ฎ๐๐ฅ๐-๐๐ก๐๐๐ค๐ฌ ๐ฆ๐ฒ ๐๐ซ๐๐ง๐ ๐๐จ๐ญ๐๐ฅ.
So, in my example above...
When I added that Bonus Schedule at the last minute,
The =SUM(range)/2 formula would have told me โ
"Yo! You forgot to update your Grand Total formula!"
And saved me from this mistake.
**********************
So, next time you're building a schedule like this, try it!
You just grab the whooooooooooole range, and divide by 2.
90% of all spreadsheets contain errors.
Remove one from yours.
โChris