#DIV/0! error in a formula with INDIRECT and MATCH functions

[rfinnegan]

Hi All:

I have the following formula that is working on all 1800 lines of my spreadsheet, except for 11 lines (266-276). On these lines I get a #DIV/0! error.

=IF($D272="Store Total","",(W272-SUMIF($B$19:$B$1999,$B272,W$19:W$1999)/COUNTIF($B$19:$B$1999,$B272))/STDEV.P(INDIRECT("w"&MATCH($B272,$B$19:$B$1999,0)&":"&"w"&MATCH($B272,$B$19:$B$1999,1)))*(1.5))

Here's what is in each row that is referenced in the formula:
D is the cashier's last name, or the 'Store Total". If "store total" is present, I don't want anything in the cell. this part of the formula is working.
B is the Store number. Store 20 happens to be the problem store.
W is the percentage of refunds to sales given by each cashier.

The goal of the formula is to if each cashier's refunds to sales ratio is within a reasonable deviation from the store's refunds to sales ratio.
Again, it's working for all stores before store 20, and all stores after store 20.

Any help is appreciated.

 

[Robby19]

I would imagine that you are receiving this error because the cashiers at store 20 have not given any refunds. Thus you are receiving a Divide by 0 (#Div/0!) error. You could include an =IFERROR formula to remove the error and display as a 0 instead.

 

[rfinnegan]

I would imagine that you are receiving this error because the cashiers at store 20 have not given any refunds. Thus you are receiving a Divide by 0 (#Div/0!) error. You could include an =IFERROR formula to remove the error and display as a 0 instead.

they've all issued refunds. the ratio of refunds to sales ranges from 1.75% to 3.76%. And there are two stores that have no refunds, but I'm not getting an error on those stores.