Running % of absolute difference

[sm_Napa]

I have a table of data with value 3 columns: two columns have values, the third column has the difference bteween these values, can be a negative or positive number. Without sorting the data, I want to calculate the running % of total of the difference column ignoring the sign (ie treating the difference as absolute). The purpose is this: the differences are 'variances' between actual and budget. I want to pick those items with highest absolute differences that account for at least 80% of all differences. I can do this by adding a column to convert the differences to absolute, add another column with Rank and a third column that has the running total based on the preceding two columns. But I want to do all the culations in a single column, without having to add 2 new columns (for absolute difference and rank).

 

[Gerald Higgins]

Hi, welcome to the board.

Your request may well be doable but it's difficult to be certain because your request is not as clear as it could be.
Could you please post back a SMALL sample of your data, showing exactly what you want to do, and what the results should be ?
If you don't want to post real data, make some up.

 

[Eric W]

Gerald is right, your request is a bit vague. But here's my first stab at it, let me know if it's close:

	A	B	C	D	E
1	10	9	1		73%
2	20	22	-2
3	30	33	-3
4	40	35	5
5	50	57	-7
6	60	44	16	X
7	70	70	0
8	80	81	-1
9	90	95	-5
10	100	110	-10	X
11	110	100	10	X
12	120	121	-1
13	130	100	30	X
14	140	145	-5
15	150	145	5
16	160	167	-7
17	170	170	0
18	180	180	0
19	190	210	-20	X
20	200	170	30	X

<tbody>
</tbody>

Sheet1​

D1: =IF(SUM(IF(ABS($C$1:$C$20)>=ABS(C1),ABS($C$1:$C$20)))>SUM(ABS($C$1:$C$20))*80%,"","X")

This is an array formula, confirm with Control+Shift+Enter. Then copy down the column. The values that have an X beside them are part of the top 80% variance. And as you can see by the green coloring, you can use this formula in Conditional Formatting.

E1: =SUM(IF(D1:D20="X",ABS(C1:C20)))/SUM(ABS(C1:C20))
with Control+Shift+Enter.

The values marked with an X probably won't hit 80% exactly, so I created this formula to see how close to 80% we got. The next lower value to be included would be ABS(-7), twice, which would raise the percentage to 82%.

Let us know if this is close at all.

 

[sm_Napa]

My apologies, I'll try again with same data. So I want to generate the output in Column G without having to create columns E and F

Excel 2013 32 bit

	A	B	C	D	E	F	G
3	Item	Actual	Budget	Difference	Abs Diff	Rank	Running %
4	A	7,310	9,700	(2,390)	2,390	16	98.9%
5	B	-	1,100	(1,100)	1,100	17	99.7%
6	C	16,104	16,362	(258)	258	18	99.9%
7	D	32,209	21,600	10,609	10,609	5	64.1%
8	E	47,553	64,800	(17,247)	17,247	3	48.2%
9	F	2,655	5,400	(2,745)	2,745	12	91.1%
10	G	16,105	21,600	(5,495)	5,495	9	84.2%
11	H	18,948	10,800	8,148	8,148	6	70.2%
12	I	26,941	16,200	10,741	10,741	4	56.2%
13	J	378,182	351,000	27,182	27,182	1	20.3%
14	K	35,024	32,400	2,624	2,624	15	97.1%
15	L	106,419	86,400	20,019	20,019	2	35.3%
16	M	-	-	-	-	22	100.0%
17	N	-	2,700	(2,700)	2,700	14	95.2%
18	O	198,802	202,500	(3,698)	3,698	10	87.0%
19	P	5,299	5,400	(101)	101	19	100.0%
20	Q	34,767	27,000	7,767	7,767	7	76.0%
21	R	89,122	86,400	2,722	2,722	13	93.1%
22	S	5,272	10,800	(5,528)	5,528	8	80.1%
23	T	2,651	5,400	(2,749)	2,749	11	89.1%
24	U	32	35	(3)	3	21	100.0%
25	V	222	215	7	7	20	100.0%
26
27	TOTAL	1,023,617	977,812	45,805	133,833

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[sm_Napa]

Eric W, that formula acheives what I want (I saw your reply after I posted my data). Thank you! Still need to decipher the formula logic but the output is correct!

 

[Eric W]

I wasn't too far off. Put this formula in G4 and copy down:

=SUM(IF(IFERROR(ABS($D$4:$D$25),0)>=IFERROR(ABS(D4),0),IFERROR(ABS($D$4:$D$25),0)))/SUM(IFERROR(ABS($D$4:$D$25),0))

confirmed with Control+Shift+Enter.


I just saw your latest reply. Let me know if you need help figuring out how it works.

 

[Tetra201]

@Eric W:

Just to mention: those minus signs are actually zeros under Accounting format.
So, your nice formula would work without the IFERROR's.

 

[Eric W]

@Eric W:

Just to mention: those minus signs are actually zeros under Accounting format.
So, your nice formula would work without the IFERROR's.

Good to know! When I did the copy/paste, they showed up as a dash, which caused errors without the IFERROR. The formula would be

=SUM(IF(ABS($D$4:$D$25)>=ABS(D4),ABS($D$4:$D$25)))/SUM(ABS($D$4:$D$25))

without them. Thanks for the tip.