Excel Magic Trick 1397: Excel Count Formulas for Running Total and % of Running Total & Ogive Chart

Download Files:

Start File:

See how to create formulas and a chart for Category Totals, Running Totals, and % Running Totals:

1. (00:15) Introduction. Why you might use formula rather than a PivotTable.
2. (02:00) Create formulas for Lower and Upper Limits
3. (03:23) Text Formula for Category Label
4. (04:15) COUNTIFS function to get Frequency Count
5. (07:23) FREQUENCY Array Function to get Frequency Count
6. (09:55) SUM function and expandable range for Running Total, Cumulative Frequency
7. (11:10) % Running Total, % Cumulative Frequency Formula
8. (11:55) Ogive Chart using a Line Chart and Error Bars
9. (18:42) Summary

Reference Videos:
Excel Magic Trick 1396: PivotTable Show Values As: Running Total and % of Running Total (2 Examples)
Excel Magic Trick 1397: Excel Count Formulas for Running Total and % of Running Total & Ogive Chart
Excel Magic Trick 1397 Part 2: Formulas: Monthly Revenue, Running Total & % Running Total & Chart
Excel Magic Trick 1397 Part 3: % Frequency Running Total Formula w Irregular Increments & X-Y Scatter
Excel Magic Trick 1398: DAX Formulas for Running Total and % of Running Total & other DAX Tricks

[LeilaGharani]

Great! Error bars are one of my favorite chart techniques. For the error bar options, you can also use percent 100%. Just another way to get the same result and avoid the custom values. Thank you for the great video!

[kevinmcaleer]

Great video! I always love the ones you do with array formulas - they are pure magic.

[shoeshines]

This was an awesome tutorial. It was a neat trick to use COUNTIFS to calculate the Frequency. Another cool trick was using the Error Bars feature, or tricking it, to get the lines from the X-Axis each point on the line. Really cool stuff.

[georgebob]

Keep them coming man! I use your tutorials on a everyday basis. I really want to learn more and more about the data model functions. I feel like I can use the powerful tools to do a lot of data analysis since that is my main function.

[michaelfullard]

Keep up the excellent work Mike, thanks.

[mohamedchakroun]

If there is 10000 like to make i will make it. Great Mike as always

[ikark]

It's really cool trick with error bars!

[LadyBlueRR]

I watched your check book register video (thank you for that one :) ) but now I am trying to create my own family budget in excel and Im trying to have a summary page that would show my totals at quick do I get the balance of the running total to show up on the summary page AS WELL AS, how do I get the totals from all my separate tables i.e. projected and actual totals to show up there as well....i keep getting an error message saying that I must put(') before my formula for it not to be a formula even though i want it to be a formula

[chestezzz]

can you tell me a way to find the difference between two sets of running totals (standard running total vs actual running total).