Getting #Value! Error rather than value in cells derived from formula includng blank cell

[Securitysports]

AN11 = L11 - M11

Used =IF(L11=0,"",L11-M11) to forward blank cell to make AN11 a blank cell.

Problem is that AO11 returns this #Value!

Function in AO 11 is:

=RANK(AN11,$AN$1:$AN$15)

The problem gets trickier as there are values in the column that are positive (5) and (0) and negative (-11) etc. This makes using a formula such as:

=IF(Z11=0,0,Z11+AB11)

undesirable as it returns 0's which in turn causes issues when I plan to use this formula in column AP.

=MATCH(SMALL(AO:AO,ROW()),AO:AO,FALSE)

So far this is the most complicated issue that I have come across ... I have looked through help on Excel and am having a hard time finding any function that resolves this one.

Thank you for your assistance with this issue,
Leonard

[FDibbins]

if you have an error in your rank range "=RANK(AN11,$AN$1:$AN$15)", it will cause the rank() to return an error as well

However, because this is fairly involved, it would be easier to fin d the rpoblem if we could see a sample workbook?

[Securitysports]

Attached I believe is the work book for this situation.

Thank you again for your assistance,
Leonard

[Securitysports]

I just saw another issue on the work book ... in column D the problem starts with
cells 2 and 8 with a value of 6 when it should be 7. This is turn starts the domino's
falling as all the values from 5 on down are off the mark as well.

I have been able to work through many issues using the help manual on Excel but
about 20 percent of the project has issues that I have not been able to find a
way out of. I really do appreciate all the assistance that has come my way.

Thank you again for all the help in getting this spread sheet up and running,
Leonard

[FDibbins]

change column D to this...
=IF(C1="","",RANK(C1,$C$1:$C$15))
and then change column E to this...
=IF(D1="","",MATCH(LARGE(D:D,ROW()),D:D,FALSE))

edit: the reason the numbers are not matching as you want is because you have duplicate values (2x 7's and 2x -8's), so you are missing a 2nd and a 7th ranking)

To overcome this, add a helper to break the ties, use this, copied down (I used F)...
=IF(C1="","",C1+COUNTIF($C$1:C1,C1)/100)
Then your rank formula changes to...
=IF(F1="","",RANK(F1,$F$1:$F$15))

[Securitysports]

Thank you FDibbins ... that took care of a huge part of this issue.

A little more clean up and this is going to be a huge time saver.

Thank you again,
Leonard

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

[FDibbins]

post self-deleted