Total Difference between Two Columns

[jarett]

I think I am calculating my average differences all wrong for my spreadsheets and I am drawing a blank on how to get the correct number. Right now I am calculating the difference per row (COLUMN I) and then getting the average of the column that is calculating the row's difference. Depending on the qty in the row some of the percentages are way off if there is a small qty (I25). I think I should be getting the totals of the 2 columns I am comparing(K5,=(ABS(SUM(H:H)-SUM(E:E))/SUM(E:E))) , so I want to make sure I am thinking of this correctly, if I do it the second way I would get a 1.34% discrepancy compared to being off 7% the current way.

10_24_2023_2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%	Inventory Discrepence %	7%
2	ItemCode	ItemCodeDesc	WarehouseCode	LastPhysicalCountDate	QuantityOnHand	Qty on Shelf	WIP Additions	Updated QTY	Percent Difference
3	10012932-BROWN-10.5D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	16	16		16	0%		New Way
4	10012932-BROWN-10.5EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	15	15		15	0%
5	10012932-BROWN-10D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	8	9		9	13%		1.34%
6	10012932-BROWN-10EE	MNS RIGTEK WIDE SUARE OILD BRN	000	10/5/23	8			0
7	10012932-BROWN-11.5D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	10	10		10	0%
8	10012932-BROWN-11.5EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	10	10		10	0%
9	10012932-BROWN-11D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	13	13		13	0%
10	10012932-BROWN-11EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	10	11		11	10%
11	10012932-BROWN-12D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	10	11		11	10%
12	10012932-BROWN-12EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	17	17		17	0%
13	10012932-BROWN-13D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	7	8		8	14%
14	10012932-BROWN-13EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	8	9		9	13%
15	10012932-BROWN-14D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	4	5		5	25%
16	10012932-BROWN-14EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	5	5		5	0%
17	10012932-BROWN-7.5D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	4	4		4	0%
18	10012932-BROWN-7.5EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	3	3		3	0%
19	10012932-BROWN-7D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	3	4		4	33%
20	10012932-BROWN-7EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	4	4		4	0%
21	10012932-BROWN-8.5D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	6	6		6	0%
22	10012932-BROWN-8.5EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	9	10		10	11%
23	10012932-BROWN-8D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	5	5		5	0%
24	10012932-BROWN-8EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	8	9		9	13%
25	10012932-BROWN-9.5D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	9	9		9	0%
26	10012932-BROWN-9.5EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	12	13		13	8%
27	10012932-BROWN-9D	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	8	10		10	25%
28	10012932-BROWN-9EE	MNS RIGTEK WIDE SUARE OILD BRN	000	8/8/23	12	11		11	8%
IM_WH_COUNT

Cell Formulas
Range	Formula
C1	C1	=AVERAGE(I3:I2434)
H3:H28	H3	=[@[Qty on Shelf]]+[@[WIP Additions]]
I3:I28	I3	=IF(E3=0,"",(IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])))
K5	K5	=(ABS(SUM(H:H)-SUM(E:E))/SUM(E:E))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Cell Value	=0	text	NO
C1	Cell Value	between 0.009 and 0.05	text	NO
C1	Cell Value	between 0.051 and 0.15	text	NO
C1	Cell Value	between 0.151 and 0.25	text	NO
C1	Cell Value	>=25.1%	text	NO

 

[GuzmanServices]

You are correctly calculating the average in Cell K5.
In effect, the same formula can be used on Cell I30, assuming that is the Row for your total amounts:

=ABS((SUM(H:H)-SUM(E:E))/SUM(E:E))