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.
I also run into an issue when all the lines have a 0 percent diff, I get this error in C1, solution for both?
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 |