Average Issue and Error

[jarett]

I feel like I am not capturing the correct average in my formula, if the row is correct and I have a percent difference of 0, it does not reduce my value in C1 "the average". If rows have a percent diff of 0 it should help get my Discrepancy average closer to 0.

IM_WH_COUNT.xlsx
	A	B	C	D	E	F	G	H
1	Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%	Inventory Discrepence %	21%
2	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOnHand	Qty on Shelf	WIP Additions	Updated QTY	Percent Difference
3	CIS01-CDKW038-STAINLESSSTEEL	20oz. Viking Tumbler, CDKW038	000	19	19	0	19	0%
4	CIS01-WTV-LG11-BLACK	Lockwood Auto Open Golf Umbrel	000	10	10	0	10	0%
6	DIA01-3400-TRUETREE	Original Tally Book with Diamo	000	2	2	0	2	0%
7	DIA01-3400-WHITE	Original Tally Book with Diamo	000	13	13	0	13	0%
8	DIA01-3410-GRAY	Original Tally Book Junior wit	000	3	3	0	3	0%
9	DIA01-3410-TRUETREE	TRUE TREE JR TALLY BOOK WITH D	000	72	72	0	72	0%
10	DIA01-3410-WHITE	Original Tally Book Junior wit	000	150	150	0	150	0%
11	DIA02-3410-WHITE	Original Tally Book Junior wit	000	29	29	0	29	0%
12	GAT01-PT-20W-WHITE	Patriot 20oz Tumbler LOGO: Gat	000	42	42	0	42	0%
15	DIA01-3310-GREEN	Tri-Fold Regular Size Tally Bo	000	72	71	0	71	1%
17	CIS01-767-01-WH	Tall Java Latte Ceramic 16 oz.	000	67	63	2	65	3%
18	GAT01-RTIC-30-WHITE	RTIC 30 OZ TUMBLER	000	28	28	3	31	11%
19	DIA01-3308-TRUETREE	WIRE-O TALLY BOOK WITH DIAMOND	000	32	10	0	10	69%
IM_WH_COUNT

Cell Formulas
Range	Formula
C1	C1	=AVERAGEIF(H3:H5009,">0")
G3:G4,G6:G12,G15,G17:G19	G3	=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H4,H6:H12,H15,H17:H19	H3	=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])

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

I also run into an issue when all the lines have a 0 percent diff, I get this error in C1, solution for both?

07_17_2023_4.xlsx
	A	B	C	D	E	F	G	H
1	Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%	Inventory Discrepence %	#DIV/0!
2	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOnHand	Qty on Shelf	WIP Additions	Updated QTY	Percent Difference
3	CIS01-CDKW038-STAINLESSSTEEL	20oz. Viking Tumbler, CDKW038	000	19	19	0	19	0%
4	CIS01-WTV-LG11-BLACK	Lockwood Auto Open Golf Umbrel	000	10	0	10	10	0%
6	DIA01-3400-TRUETREE	Original Tally Book with Diamo	000	2	2	0	2	0%
IM_WH_COUNT

Cell Formulas
Range	Formula
C1	C1	=AVERAGEIF(H3:H5009,">0")
G3:G4,G6	G3	=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H4,H6	H3	=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])

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

 

[Kaper]

Well, are you sure you shall use AverageIf? from your description it sounds that presence of zeros in column H shall decrease the result, so why not use just:

=AVERAGE(H3:H5009)

In other words (especially if the above is not "the solution": what result shall be obtained for your sample data (13 rows of real data and only 4 non-zero) and how have you calculated it.

And what is in hidden rows, like 5th, 13th etc.

 

[jarett]

Well, are you sure you shall use AverageIf? from your description it sounds that presence of zeros in column H shall decrease the result, so why not use just:

=AVERAGE(H3:H5009)

In other words (especially if the above is not "the solution": what result shall be obtained for your sample data (13 rows of real data and only 4 non-zero) and how have you calculated it.

And what is in hidden rows, like 5th, 13th etc.

I used AVERAGEIF because I was getting an error when I would error on the percentage difference formula, example below, so I guess my focus should be on that field to solve that error first. The hidden rows do not have a value in the cells.

IM_WH_COUNT.xlsx
	A	B	C	D	E	F	G	H
1	Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%	Inventory Discrepence %	#DIV/0!
2	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOnHand	Qty on Shelf	WIP Additions	Updated QTY	Percent Difference
3	08-0003188	NO HOLES-31-1/2 X 500 .005 WHT	000	0			0
4	08-0007208	120 in.SPACING-31-1/2 x 500' .	000	3	3	0	3	0%
5	08-0007208	120 in.SPACING-31-1/2 x 500' .	003	0			0
6	08-0007209	34 in.SPACING-31-1/2 x 500' .0	000	8	2	5	7	13%
7	08-0007211	60 in.SPACING-31-1/2 x 500' .0	000	0	1	0	1	#DIV/0!
8	08-0007212	60 in. SPACING-19 X 500' .005	000	4	4	0	4	0%
IM_WH_COUNT

Cell Formulas
Range	Formula
C1	C1	=AVERAGE(H3:H5010)
G3:G8	G3	=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H8	H3	=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])

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

 

[Kaper]

So you probably shall use function AGGREGATE(1, ... which is just an AVERAGE, but with not taking into account cells with type of content described in second argument (I suggest 7, so skip errors and hidden cells):

=AGGREGATE(1,7,H3:H5010)