Selectively reference a cell range, but not include the cells containing error code text

[Zdog]

Hi,

Please can someone help me with this issue, I am stumped.

See attached excel containing a sample of data to help explain my point.

The formulas in column D and E are not working because column C contains data that gives a completely legitimate #DIV/0! error in column D. Because of this error, column E cannot complete the percentrank formula, as #DIV/0! Is not a number, obviously.

How can I make the percentrank formula in column E ignores the cells with #DIV/0! errors in column D? i.e. how can I make it reference D2:D11 AND D13:15 AND D17:19, but not D12 or D16? Note, the actual dataset is much bigger, so I cannot just manually edit the percentrank reference area.

Imaginary pennies (but infinite thanks) for your thoughts.

EDIT:
Sorry, I should have said in the original message that I have already tried AlKey's suggestion - "Replace you formula in D2 with: =IFERROR(100*(B2/C2),0)". It works to fix the formulas; however, the percentrank needs to not include those '0s', because if it does, the '0s' trick the percentrank into thinking the dataset is bigger than it is, which effects the final column E calculations. Basically, the numbers in column E are different if I do or do not include '0s' in column D.

[AlKey]

Replace you formula in D2 with: =IFERROR(100*(B2/C2),0)

[Zdog]

Hi AlKey,

Sorry, I should have said in the original message that I have already tried that. It works to fix the formulas; however, the percentrank needs to not include those '0s', because if it does, the '0s' trick the percentrank into thinking the dataset is bigger than it is, which effects the final column E calculations. Basically, the numbers in column E are different if I do or do not include '0s' in column D.

I'll edit the original question to highlight that.

Thanks.

[AlKey]

Do you need to replace 0 and errors with blanks?
Try this:
in D2

Please Login or Register  to view this content.

in E2

Please Login or Register  to view this content.

[Zdog]

Hi AlKey,

Thanks, that almost fixes things - it gets rid of the error messages. Cant believe I didn't think of using 'blank' - I am tired... I did need to tweak it a bit, because it was also giving blanks for rows 3 and 4, which I want to remain '0'.

However, these empty cells cause issue in upstream calcs. For example,

=IF(E2>=0.996,"Y","N") should only say 'Y' for greater than 0.996 (99.6%). However, it now also gives a 'Y' for the blank cells.

[FlameRetired]

What if in D2

Please Login or Register  to view this content.

Column E returns 0s where there are 0s in column C.

All

Please Login or Register  to view this content.

return "N" except 'Country' 5 (row 6).

What am I missing?