Average Issue and Error

jarett

Board Regular
Joined
Apr 12, 2021
Messages
179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGH
1Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%Inventory Discrepence %21%
2ItemCodeItemCodeDescWarehouseCodeQuantityOnHandQty on ShelfWIP AdditionsUpdated QTYPercent Difference
3CIS01-CDKW038-STAINLESSSTEEL20oz. Viking Tumbler, CDKW03800019190190%
4CIS01-WTV-LG11-BLACKLockwood Auto Open Golf Umbrel00010100100%
6DIA01-3400-TRUETREEOriginal Tally Book with Diamo00022020%
7DIA01-3400-WHITEOriginal Tally Book with Diamo00013130130%
8DIA01-3410-GRAYOriginal Tally Book Junior wit00033030%
9DIA01-3410-TRUETREETRUE TREE JR TALLY BOOK WITH D00072720720%
10DIA01-3410-WHITEOriginal Tally Book Junior wit00015015001500%
11DIA02-3410-WHITEOriginal Tally Book Junior wit00029290290%
12GAT01-PT-20W-WHITEPatriot 20oz Tumbler LOGO: Gat00042420420%
15DIA01-3310-GREENTri-Fold Regular Size Tally Bo00072710711%
17CIS01-767-01-WHTall Java Latte Ceramic 16 oz.00067632653%
18GAT01-RTIC-30-WHITERTIC 30 OZ TUMBLER000282833111%
19DIA01-3308-TRUETREEWIRE-O TALLY BOOK WITH DIAMOND000321001069%
IM_WH_COUNT
Cell Formulas
RangeFormula
C1C1=AVERAGEIF(H3:H5009,">0")
G3:G4,G6:G12,G15,G17:G19G3=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H4,H6:H12,H15,H17:H19H3=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1Cell Value=0textNO
C1Cell Valuebetween 0.009 and 0.05textNO
C1Cell Valuebetween 0.051 and 0.15textNO
C1Cell Valuebetween 0.151 and 0.25textNO
C1Cell Value>=25.1%textNO


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
ABCDEFGH
1Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%Inventory Discrepence %#DIV/0!
2ItemCodeItemCodeDescWarehouseCodeQuantityOnHandQty on ShelfWIP AdditionsUpdated QTYPercent Difference
3CIS01-CDKW038-STAINLESSSTEEL20oz. Viking Tumbler, CDKW03800019190190%
4CIS01-WTV-LG11-BLACKLockwood Auto Open Golf Umbrel00010010100%
6DIA01-3400-TRUETREEOriginal Tally Book with Diamo00022020%
IM_WH_COUNT
Cell Formulas
RangeFormula
C1C1=AVERAGEIF(H3:H5009,">0")
G3:G4,G6G3=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H4,H6H3=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1Cell Value=0textNO
C1Cell Valuebetween 0.009 and 0.05textNO
C1Cell Valuebetween 0.051 and 0.15textNO
C1Cell Valuebetween 0.151 and 0.25textNO
C1Cell Value>=25.1%textNO
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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:
Excel Formula:
=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.
 
Last edited:
Upvote 0
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:
Excel Formula:
=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
ABCDEFGH
1Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%Inventory Discrepence %#DIV/0!
2ItemCodeItemCodeDescWarehouseCodeQuantityOnHandQty on ShelfWIP AdditionsUpdated QTYPercent Difference
308-0003188NO HOLES-31-1/2 X 500 .005 WHT00000 
408-0007208120 in.SPACING-31-1/2 x 500' .00033030%
508-0007208120 in.SPACING-31-1/2 x 500' .00300 
608-000720934 in.SPACING-31-1/2 x 500' .0000825713%
708-000721160 in.SPACING-31-1/2 x 500' .00000101#DIV/0!
808-000721260 in. SPACING-19 X 500' .00500044040%
IM_WH_COUNT
Cell Formulas
RangeFormula
C1C1=AVERAGE(H3:H5010)
G3:G8G3=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H8H3=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1Cell Value=0textNO
C1Cell Valuebetween 0.009 and 0.05textNO
C1Cell Valuebetween 0.051 and 0.15textNO
C1Cell Valuebetween 0.151 and 0.25textNO
C1Cell Value>=25.1%textNO
 
Upvote 0
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):
Excel Formula:
=AGGREGATE(1,7,H3:H5010)
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top