Please help me in the following query, my excel table contains three columns customer(Column A), Daily sales (Column B), Cumulative Sales ( column C).
I enter daily sales made to customers and taking previous sales in column c i add daily sales to arrive cumulative sales . This i have to repeat to n number of customers. Is there any other way to save my time so that cumulative sales is calcuated automatically when i give daily sales.
Hi -
I have done a lot of reseach regarding tiered commision formulas in excel but none of them addresses how to calculate tiered commisions on cummulative monthly sales.
The annual quota is $1,200,000.
There are four commision tiers
0 - 80% of quota = 3% commision
80%- 100% of quota = 9% comission
100%-110% of quota = 5% commision
110%- > of quota = 8% commision
I have variable sales made in each of 12 months. I need to calculate the comission on cummulative sales basis each month.
For instance if in first month the whole $1,200,000 is achieved the comission will be as follow:
3% on 960000
9% on rest 240000
On other hand if the sales for first month were $20,000 and sales for seond month were $1,400,000
then the commision for first month would be 3% on $20,000
and
comission for second month would be as follow
3% on 940,000 (960000-20000) as commision is paid on annual cumulative sales.
9% on 260,000
5% on 12,000
8% on 80,000
Now this is my dilema as usually tiered comission are paid on monthly or quarterly basis and not on commulative annual sales. I can seem to get my head around this.
Any help would be appreciated.
Thanks
AL!
Hello,
Using Excel 2007.
I have a pricing scale based on total cumulative annual sales:
0-1000 units - $3/unit
1001-2000 units - $2/unit
2001- 3000 units - $1/unit
3001 units and over - $0.5/unit
I place 1 order per month, different quantity every time.
What I need to calculate is how much I will be paying per unit at any given month in a year.
For ex:
Cumulative sales in May are 900 units and cumulative sales in June happen to be 1200 units (a purchased of 300 in June), then June unit price is ((100*3)+(200*2))/300. In order to do what-if scenarios I need to find a formula that could do this calculation given the pricing scale.
Does anyone know how this can be done?
Thank you
Hi
Is it possible to create a cumulative frequency table with Pivot Tables?
e.g I have a set of data summarised into 5 segments with units #
I can use the pivot table options to produce the 2nd column (% of Total in Column) or 3rd Column ( running total) but I cannot find a way to get the 4th column ( cumulative % running total ?)
data# %total run to ***%
a 40 40% 40 40%
b 20 20% 60 60%
c 30 30% 90 90%
d 5 5% 95 95%
e 5 5% 100 100%
I want to be able to use the data to produce charts without having to copy and paste data to create seperate charts.
Hi
I have already generated a pivot table as follows:
Name --- Month (which will be from Col B to M) Total (Col O)
In order to get the cumulative amount for any given period (under
Pivot Table) I will need to tick which are the months I want to
get the cumulative data. I find this a bit time consuming.
Is there any way I could get a formula side by side with the pivot
table that can give me any cumulative month. My present way of
doing is to have another additional 12 columns from Col R onwards.
Instead of having another additional column in the data tab I could
generate the cumulative amount in another area next to the pivot table.
Example when in May I want to get the Jan-May cumulative without
going into the pivot table to tick or untick.
Thanks
Hi
I have created a Pivate table which count the number of addition in a group and number of deletion in a group for a given month
for deletion I used negating figures thus The Grand total in net of Addtion and deletion
I would like to add another column in pivot table if possible a Formula Field so that i can calculated the Cumulative Total in Pivot table
Regards
Balendra Kumar
I am currently using Excel 2003 to create a pivot table from an Access database. It is for a project which spans over a few years. I need to have a cumulative sum trending over the span of the project by month/year. When I try to group month and year, the running total restarts each year. I tried to do a running total over a Month/Year field but when I do a pivot chart, it does not recognize it as a date and plots the spacing between Sept. and Jan. the same as Jan. and Feb. This gives me very misleading trending charts. Once I figure out the solution, it needs to be able to happen every time as I am creating this for someone else who just wants to click "refresh" and have updated trending charts.
Thank you!
I know how to get a cumulative count by month using a pivot with the grouping option and "Running total in" pivot field options (link), but how can I get a cumulative average?
i.e. in the linked sheet (scroll down to the zipped solution), the Jan avg would be 47.967, and the Feb cumulative average is 47.42 (as opposed to the feb avg of 46.83).
I'm trying to avoid the CSE formulas as they are slow (I have multiple cumulative averages to calculate from 500 rows), messy when you have 4 criteria, and you can't drill down.
Hi,
I am recording the downtime of machines and putting into order of most - least downtime for each of the reasons recorded against the machine being monitored. This works fine when sorting the ivot table by the total downtime column.
I have been asked if I can add a cumulative percentage line to the pivot chart as this is the standard used for pareto analysis (80% of losses are attributed to 20% of reasons) I can obviously get a percentage of each reason code but cant figure out how to do a cumulative percentage....
II can do it externally to the pivot table which works fine but when you return the data to the pivoot table, it gets sorted by the downtime column and obviously upsets the order of the cumulative count.
I hope that makes sense!!
BTW, Merry Christmas and a Happy, Peaceful and Prosperous New Year to you all!!
I have created a pivot table from date in 2 different sheets.
Both sheets only has date in common. Report is working fine.
My report has filed called - "Amount Receivble" (AR).
Now, i wish to to have AR and also a cumulative total of AR.
But I am not finding how to only insert AR again and do the cumulative total for that newly inserted field only.
pl. assist.
regards, nishith
