Excel Complicated Cumulative Division Problem

[Lukas Friedrich]

Hello together,

I want to show a cumulative division between to columns in a pivot table. A division in percentage between invoied orders and total order. This should be based on the last days of a month. Example given in my first attachment.

As you can see, the percentages are wrong, because they are based on the calculation done on my data set. The percentages are right for the normal order number, but not for the cumulative order. I have an extra table for non cumulative showing, but I still need this in a cumulative manner and to also show this on a graph. On my second attachment you can see my data set that I build. If I cant do this calculation in a pivottable, maybe a formula in the data set can help me out.

Thank you in advance for your help!

 

[Lukas Friedrich]

Hello together,

I want to show a cumulative division between to columns in a pivot table. A division in percentage between invoied orders and total order. This should be based on the last days of a month. Example given in my first attachment.

As you can see, the percentages are wrong, because they are based on the calculation done on my data set. The percentages are right for the normal order number, but not for the cumulative order. I have an extra table for non cumulative showing, but I still need this in a cumulative manner and to also show this on a graph. On my second attachment you can see my data set that I build. If I cant do this calculation in a pivottable, maybe a formula in the data set can help me out.

Thank you in advance for your help!

 

[awoohaw]

@Lukas Friedrich , welcome to the forum.
The forum has an easy to use add-in called xl2bb (link below) that allows you to post mini worksheets. These really help the forum help you. With images, which do convey some information, the forum needs manually recreate your scenario which takes time and can have errors/typos.

So, please use the add in and post a mini worksheet. If you cannot install the add in, there is a forum chat for testing it out (link also below). In a worst case please post the data in a table (label rows and columns please), and copy any formulas into your text (and say which cell).

Then also show your expected results as well.


And regardless of how you post the data, please sanitize it for personal security.

thanks in advance.

 

[Lukas Friedrich]

@Lukas Friedrich , welcome to the forum.
The forum has an easy to use add-in called xl2bb (link below) that allows you to post mini worksheets. These really help the forum help you. With images, which do convey some information, the forum needs manually recreate your scenario which takes time and can have errors/typos.

So, please use the add in and post a mini worksheet. If you cannot install the add in, there is a forum chat for testing it out (link also below). In a worst case please post the data in a table (label rows and columns please), and copy any formulas into your text (and say which cell).

Then also show your expected results as well.


And regardless of how you post the data, please sanitize it for personal security.

thanks in advance.

Thanks for the quick answer. If I try to create a mini sheet, it shows me an error message "7 - Out of memory". It also shows with sheets with low volume on data.

 

[awoohaw]

i'm not sure what that means. But, it is an add in that uses up memory in your computer. Maybe close some idle applications or worksheets.
only highlight the cells you need to paste (there is a limit to the number of characters or cells pasted). then from the xl2bb tab click mini-sheet.
do you see these items:

 

[awoohaw]

of course you can post as a table. just highlight the cells, copy then paste here. But you need to provide formulas (and locations), row numbers, column letters, and expected results.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Complicated Cumulative Division Problem
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Lukas Friedrich]

Book1
	A	B	C	D	E	F	G
1	GI/IR Date	Global Region	Product Group	Invoiced	Order Total	Cumulative Invoiced	Cumulative Order Total
2	19.09.2022	Americas	SO	1	1	19	21
3	19.09.2022	China	SO	1	1	19	21
4	19.09.2022	China	SO	1	1	19	21
09_22

Cell Formulas
Range	Formula
F2:F4	F2	=IF(ISNUMBER([@[GI/IR Date]]),SUMIF([GI/IR Date],"<="&[@[GI/IR Date]],[Invoiced]),"")
G2:G4	G2	=IF(ISNUMBER([@[GI/IR Date]]),SUMIF([GI/IR Date],"<="&[@[GI/IR Date]],[Order Total]),"")

Book2
	A	B	C	D
5	2022	260	500	52,00%
6	Sep	70	121	57,85%
7	day -9	19	21	90,48%
8	day -8	10	15	66,67%
9	day -7	9	10	90,00%
10	day -6	5	7	71,43%
11	day -5	8	12	66,67%
12	day -4	5	10	50,00%
13	day -3	6	13	46,15%
14	day -2	5	17	29,41%
15	day -1	3	16	18,75%
16	Okt	47	110	42,73%
17	day -9	7	11	63,64%
18	day -8	7	13	53,85%
19	day -7	4	9	44,44%
20	day -5	2	5	40,00%
21	day -4	4	10	40,00%
22	day -3	2	6	33,33%
23	day -2	12	20	60,00%
24	day -1	3	20	15,00%
25	day 0	6	16	37,50%
26	Nov	73	161	45,34%
27	day -9	16	23	69,57%
28	day -8	6	11	54,55%
29	day -7	5	11	45,45%
30	day -6	16	20	80,00%
31	day -5	7	17	41,18%
32	day -4	8	17	47,06%
33	day -3	4	15	26,67%
34	day -2	5	10	50,00%
35	day -1	4	19	21,05%
36	day 0	2	18	11,11%
TableFull