Free Microsoft Excel 2013 Quick Reference

Calculating Cumulative Totals

I am trying to calculate cumulative totals for some data and then graph them.
I have different groups and they get a score everyday. I want to be able to calculate the total scores for each group every month cumulatively.
I have started doing this on the attached spreadsheet but have reached a stumbling block. So far I have been able to get a cumulative score for the total each month, but I cant work out how to split it by the group numbers.
In my spreadsheet, it is working upto Cell G31. When it gets to group 2, it should reset and start cumulating from 0.

Sorry about the screenshot. I tried attaching the spreadsheet but for some reason it is 2MB.

Thanks


Hi,
in my worksheet i have different kind of items with its cost. in my case which is not in order, that is, the order of items can be AABAACCBA...
I want to calculate Cumulated Total on each row. but i am not sure how to achieve this by conditional formula? the values in my sheet looks like the following,

	VB:
	
Date	        ITEM    TYPE    AMOUNT Cumulated Total 
10-Jan-07	Book	A	10	10               -value(Book) 
11-Jan-07	Pen	A	5	15               -value(Book+Pen) 
12-Jan-07	Table	B	15	15               -value(Table) 
13-Jan-07	Pencil	A	20	35               -value(Book+Pen+Pencil) 
14-Jan-07	Chair	B	25	40               -value(Table+Chair) 
15-Jan-07	Sofa	B	35	75               : 
16-Jan-07	Rose	C	20	20               : 
17-Jan-07	Calc...	A	30	65               : 
18-Jan-07	Jasmin	C	10	30               -value(Rose+Jasmin) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please find the attachment for reference. How to achieve this using conditional statement or lookups or someother? and i try to avoid macro.
any help would be greately appreciated!
Thanks.

Hi All

I need to be able to calculate values with a given value starting value gradually increasing values with a specific number of iterations to a required cumulative total....

For example start at 1000, calculate 5 additional values gradually increasing from 1000 that will give a cumulative total of 7500 and will look on a chart as a curve.

This is not to say I want to gradually increase 1000 to end up 7500, but to end with a cumulative total of 7500.

I'm stumped so any help would be really very much appreciated.

Thanks

I am trying to use excel to calculate cumulative mileage with columns set up in calendar format. My total ends up negative unless all the daily blocks are filled in. What formula could I use to calculate mileage even if all blocks are not filled in?

I have a list of transactions over several years. I need to group the
transactions by year and month to show those totals, but also run a
calculated field cumulative total over all the years. I am using a pivot
table so I can show these totals for different vendors and product types.

My problem: when I group the data, each year starts with zeros and my
calculated field doesn't calculate properly. I get the proper cumulative
total for each year, but I need it to go over all years (all groups). Any
way to do this?

Example - PT looks like this:

Year 1 item 1 + 8.00
item 2 - 6.00
cum. total + 2.00
Year 2 item 1 + 7.00
item 2 - 4.00
cum. total + 3.00 (should be +5.00)

Thank you!

Hi there
I need a formula for finding the performance in a percentage against a target saving and a cumulative total. I've tried various ones but have failed miserably. Everytime I think I've cracked it the percentage keeps going down even when I hit target or go over?!?

Help please

Attached is a sample of what is needed, it will probably be simple for you guys but it has me stumped...

I have a list of transactions over several years. I need to group the
transactions by year and month to show those totals, but also run a
calculated field cumulative total over all the years. I am using a pivot
table so I can show these totals for different vendors and product types.

My problem: when I group the data, each year starts with zeros and my
calculated field doesn't calculate properly. I get the proper cumulative
total for each year, but I need it to go over all years (all groups). Any
way to do this?

Example - PT looks like this:

Year 1 item 1 + 8.00
item 2 - 6.00
***. total + 2.00
Year 2 item 1 + 7.00
item 2 - 4.00
***. total + 3.00 (should be +5.00)

Thank you!

Hi,

I have a list of daily sales which I would like to have a cumulative total by month. Once the next month is encountered, the cumulative total will reset for that month again.

E.g. (please see attached file)
Column C is my cumulative total. The cumulative sum will reset when the month changes from Jan to Feb.

I can achieve the results using VBA but I need to distribute my report to parties whose VBA environment is disabled. So, I need to work around this with a formula.

Regards.

I am trying to use the VLOOKUP feature to calculate a total column. Here is roughly what i have:

A B C D E F G
- 1 2 3 4 5 6
a 2 3 4 5 6 7
a 3 4 5 6 7 8
a 4 5 6 7 8 9
- 5 6 7 8 9 10

a

basically i want to add up a column (eg E) only when the value has an "a" beside it. Can you please help me with the formula please?

I can't seem to figure out if this is possible. Here's the layout:

Row Fields = Products
Column Fields = Companies
Data = Units

I have selected 2 of the 50 companies to show data for. In addition to this I would like to have a column that shows the cumulative total of all 50 companies so that I can present the % of the total these 2 companies represent. If i show grand totals, it obviously only shows the total of the 2 companies.

Is there a way to do this.

thanks

I am trying to use Excel for a business plan assignment I've been given at work, and I'm trying to figure out how to calculate the total contract value on a monthly basis where new five-year contracts are signed each month.

For example, imagine that I sell 20 five-year contracts with payments of $50 per month. After the first month I have a total contract value of $60,000 (20 contracts * $50 * 60 months).

Now imagine that in the second month I sell 21 new contracts. My total contract value is $122,000 (21 contracts * $50 * 60 months + 20 contracts * $50 * 59 months).

If in the third month I sell 25 contracts the value would be $194,950 (20 * $50 * 58 months + 21 * $50 * 59 months + 25 * $50 * 60 months).

and so on...

Is there a way to calculate this in Excel without doing each month by hand?

Here is how I have my table set up:

Thanks in advance for any help...

I wonder whether integration tools are available in Excel? for example, to
use integration to calculate the total area under a curve. anyone has used
the function or knows about it? thank you expert.

I have the following formula in sheet2, which is a summary from sheet "MAIN".
This formula is in cell "A14" this is dragged down to cell"A5000". This is
done to columns A to G.

=IF($A14="","",SUMPRODUCT((MAIN!$A$11:$A$5000=$A14 )*(MAIN!$D$11:$D$5000)))

The problem Im having is when I clear contents in sheet "MAIN", it clears
the data in sheet2. Is there a way so that sheet2 keeps a cumulative total of
sheet "MAIN"?
This will enable me to just have about 100 rows in sheet"MAIN".
--
George

Hi,

I'm having some trouble displaying the cumulative total at the TOP of each
of my two stacked columns. Right now, only one of the columns have the total
value on top while the other one has it in the middle. To be more specific,
this is the exact scenario:
I'm trying to graph the revenue data of 2005 and expected of 2009. So there
are two stacked columns in this graph. And these columns can be stacked by
different product revenues. I want to show the cumulative total of each
stacked column, but the shorter 2005 column displays the total value half-way
up the column instead of at the top of the column. I used the steps
recommended by the following website:
http://www.andypope.info/charts/StackColTotal.htm
but I still can't solve this problem.

Any help would be much appreciated! And I have an example file if that
would make the question clearer.

Thanks in advance,
Jean

We are trying to track daily production at a dentist's office. Is it
possible to make the data labels on the graph display both the daily and
cumulative total at every tick mark?

Thanks for your help.

Arlen

I am looking for a formula that will allow me to run a cumulative total of inventory. For example in cell A1 I will enter the number of widgets purchased that day. In cell B1 I want to record the number of widgets previously ordered plus the new widgets so I can track the daily order and the total for the year. So Cell A1 will show the current amount of widgets ordered and cell B1 will show the total widgets ordered for the year.

If I ordered 1 widget today A1 would show 1 and B1 would show 1.

If I ordered 3 widgets tomorrow A1 would show 3 and B1 would show 4 (as the cumulative total.

from a humble widget counter

Hi there!

I have been looking in past post, and not finding the solution to what
I am trying to do.

Here is an example of the entry sheet I am trying to look into;
I have remove some extra colomns
Colom A formatted as a date format, entering data as dd-mm-year and
shown as in example

A H
1 DATE
2 may 2, 2006
3 =SUM(F2:F4)
4
5 may 3, 2006 =F5
6 May 4, 2006
7 =sum(F6:F7)
8 May 8, 2006 =F8
..
..
20 June 1, 2006
21 =sum(F20:F21)
22 june 2, 2006 =F22

So basically I enter some amount each day, some time more then once and
make a summary of it in the H colomn.

Now in another cell, I want to calculate the total for the individual
months.
I have been trying to use the formula
=SUMPRODUCT((MONTH(A2:A22)=6),H2:H22)
for the total of June, but keep getting a #VALUE error.

What am I doing wrong, or is there another for me to accomplish what I
am trying to do?
Should I be adding on each row the date instead of leaving blanks?
(tried but same result)

Thanks in advance for any help, tip and pointers to help me solve this.

Serge

Loacation Name Sales Comm
252 ABC 100 12.5
252 CCC 100 12.5
252 DDD 100 12.5
300 EEE 100 12.5
300 FFF 100 12.5
500 GGG 100 12.5
500 HHH 100 12.5
500 KKK 100 12.5

This is the data I got from external databae query. Is there any VBA code to add subtotal and also running total in the rows after evry location number changes. I mean, If I run the vba macro It will insert two rows after every location changes. One row for subtotal for the each locaion and in the next row will be the cumulative totals for all location upto that point.

Any formula to show cumulative totals?

You know the kind of thing -

first hour production 40 Cumulative total

2nd hour production 80 120

3rd hour production 100 220

Hi there

I need a formula that will calculate the total time from when a ticket was opened to closed where the open and closed dates are completely different or there are multiple days in between. For example it was opened on the 07/06/2011 11.19am and closed on the 09/06/2011 13.48pm

I'm trying to make a cumulative total formula for a dial guage i have set up to measure displacement. The gauge only goes up to 50 so it has to be reset when it approaches that value, and sometimes it doesn't work properly and is reset earlier, or just reported as not working. I would like the cumulative total to equal the cumulative total of the last reading if no reading data is added and the instrument to be reset multiple times. The formula is for the cumulative total column. The data I have set up in the sheet as follows:

Date	 Reading	Working? Reset?	Cumulative Total
25/05/2012	2			0
26/05/2012	4			2
27/05/2012	14			12
28/05/2012	27			25
29/05/2012	25			23
30/05/2012	37			35
31/05/2012	40			38
1/06/2012		No		38
2/06/2012		No		38
3/06/2012		broken		38
4/06/2012	0		reset	38
5/06/2012	2			40
6/06/2012	6			44
7/06/2012	23			61
8/06/2012		No		61
9/06/2012	23			61
10/06/2012	35			73
11/06/2012	5		YES	73
12/06/2012	3			71
13/06/2012	4			72
14/06/2012	4			72
15/06/2012		Nope		72
Any help would be tremendously appreciated!

Need help with setting the correct formula.

Column A (Starting Revenue)
Column B (Increase Revenue)
Column C (Reduce Revenue)
Column D (New Business)

I need a formula in Column F that would calculate the total commissions earned based on the following createria:

6% on Starting Revenue
3% on Reduce Revenue
12% on Increae Revenue
12% on New Business

N.B:
If there is a figure in Column A & C, then the formula should only calculate using Column A-Column C*3%.
If there is a figure in Column A & B, then the formula should be based on the values of on both columns (6% on Column A + 12% on Column B)
If there is a figure in only Column D, then the formula should be column D*12%

I have this formula in column E =(A1)*6% + (B1)*12% + (C1)*12% + (D1)*3%

With this formula it works well for everything except the Column C as it returning to values, that of Column C (3%) plus that of column A (6%). I need it to return on the 3%.

Please help.

Thank you in advance

Angus

calculate of total working time of an employee in a day

suppose the time in is mentioned in column b3 and time out in column c3 and the total has to be displayed in d3.

and one more problem
how to calculate the leaves of an employee

say date of 1st leave is mentioned in b4 and last leave in c4 and the result to be displayed in d4 and main issue say the employee has taken leave only for 1 day , then the total leaves that is in d4 it is to be displayed as 1.

please help me and it will be great help if anyone can mail the solution to my personal email-id.

Kanwal Deep Singh
kanwal_deep@yahoo.com

Hi,

This is my first post so please bear with me.

On an Excel worksheet I have a drop down list that consists of five selections. For each selection there is price association. The source data for the dropdown list is located on a separate worksheet. For three of the selections I have a predefined price and for the other two selections I would like to have the user be able to input the price as these vary based on different factors. There is a column for a quantity and in the next column I would like to have a formula calculate the total of the quantity x the price.

I have been able to use a nested IF function to get this to work partially. The real problem that I am running into is when I want the user to input their own value for the price. If they pick an item with a predefined price there is no problem because the price column populates from the data on the other worksheet. However, as soon the user inserts their own price into the price cell, it overwrites the formula. The other problem is that if the Price cell is blank it shows #VALUE! in the total calculation cell. I would like it to not show that error message if the price cell is blank.

The solution I am looking for would look like this - if the drop down list ="Item A" then leave the cell for the price blank and let the user input their own number. In the next cell calculate the total price of the cell based on the quantity selected. If the drop down list ="Item B" populate the price cell with the predefined price then do the price calculation.

I don’t know much about VBA but perhaps the solution needs to be a VBA script?

I have attached a sample workbook help explain what is going on.

Thanks.

Hi, I need to keep a cumulative total in one cell P2 as cell B2 is updated
with new entries. Thanks in advance