=SUM(range)/2

=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

Recent Posts

See All

A Statement of Cash Flows (SCF) cheat sheet ⬇️ 🟢 I like to think of the SCF like a bridge <---->. Specifically — a bridge from: ACCRUAL accounting <----> to actual CASH in the bank. See, your Income