Hi fellow Excel Addict,
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.
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.
If 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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...
1) 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...) ;
2) 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.
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...
3) Now copy this formula down as far as you expect to have data in column B.
As new values are entered in column B, a running total will appear in column C.
|"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