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.
Bookmarks