FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
September 15, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)September is flying by. Where did the summer go?

Well, technically summer isn't over for another week but some days it sure doesn't feel like summer. Lately our weather here has been flipping between summer and fall. In the past week we have had some really nice warm summer days and some really cool fall days.

The cool days are way more comfortable for running though. I am a little more than 4 weeks away from my Cape to Cabot race. This is a 20 kilometer race that runs from Cape Spear, the most easterly point of land in Canada, along the historic 500-year-old St. John's waterfront, to Signal Hill, site of the famous Cabot Tower where the first ever transatlantic wireless signal was received by Guglielmo Marconi in 1901.

I have been doing lots of hill training in preparation for those 4 big hills along the course (a total elevation gain of 550 meters). That last one, Signal Hill, in the final 2 1/2 kilometers is very steep and it's going to be brutal.

Cape To Cabot Race Course
Another Kind of Running
Today's 'Excel in Minutes' tip is about 'Creating a Running Total Formula'.
I hope you find it helpful.

Please feel free to share it with your friends—chances are they will too.

I
f you missed my 'Excel in Seconds' newsletter from Tuesday, I showed you 'A Case for Fixing Typing Mistakes'. You can read the tip here.

Keep on
Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com
 



'Excel Macros for Beginners' Online Video Course



If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"There is no beauty in sadness. No honor in suffering.
No growth in fear. No relief in hate.
It’s just a waste of perfectly good happiness."

-- Katerina Stoykova Klemer --


If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

Creating a Running Total Formula

Something that I am asked quite often is, "How can I create a running total in Excel?'

There are multiple ways to do this but here's the one that I use most times.

Let's assume all the amounts are in column B and you want to have a 'running total' in column C .

Here's what you can do...

CLICK TO DOWNLOAD THIS PRACTICE FILE

Create Running Total Formula in Microsoft Excel 2007 2010 2013 2016 3651) In column C, youll need a SUM formula that will add all the values from the top of column B down to the row to the left of your SUM formula. To do this you will use mixed references in your formula e.g. =SUM(B$2:B2). The dollar sign in the first reference to cell B2 (i.e. the top of the sum range) tells Excel not to adjust the row number as you copy the formula down the column. Although the second reference also points to cell B2, it actually refers to the bottom of the range you want to sum. Since there is no dollar sign in the second reference to B2, its row number WILL adjust as the formula is copied down the column (e.g. B3, B4, B5, etc...) ;

Create Running Total Formula Absolute Reference in Microsoft Excel 2007 2010 2013 2016 3652) Copy the formula from cell C2 down the column. Now, when you look in cell C3, you'll notice that the first reference to cell B$2 hasn't changed but the second reference has changed from B2 to B3. This formula now sums all of the cells from B$2 to B3. The SUM formula on all subsequent rows will sum the cells from B$2 down to the current row.



No Totals for Blank Rows

If you copy this formula down past the values in column B, you will see that the last calculated total is carried forward to each row with no value in column B.

Running Total Repeating For Blank Rows in Microsoft Excel 2007 2010 2013 2016 365

You can modify this formula with an IF function to show a total only when there is an value on that row in column B.

1) So, let's go back to cell C2 to adjust the original formula;

2) The logic we want the formula to use is, 'check if cell B2 is blank, and if it is, leave the running total blank, otherwise, display the result of the SUM formula'.

The new formula will be...
=IF(B2="","",SUM(B$2:B2))

3) Now copy this formula down as far as you expect to have data in column B.

Adjust Running Total Formula For Blank Rows in Microsoft Excel 2007 2010 2013 2016 365
As new values are entered in column B, a running total will appear in column C.


'Excel Macros for Beginners' Online Video Course


FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630