Thread: Rank ABS data with IF function

I'm trying to create 1 formula from a combination of both B and C formulas. Currenly my worksheet uses 2 steps: 1.B 2.C...I only want 1.

B:
=IF(OR(B2<98,B2>100),14,1)+ABS(100-B2)
C:
=RANK(C2,C2:C15,1)

Is there a formula that combines these?

Please advise.


A B C
SCORE ABS RANK
98.70 2.3 6
98.94 2.06 4
97.79 16.21 13
99.50 1.50 3
100.32 14.32 9
98.84 2.16 5
101.71 15.71 11
101.05 15.05 10
97.83 16.17 12
103.47 17.47 14
98.68 2.32 7
99.99 1.01 2
100.01 14.01 8
100.00 1.00 1

If score is < 98, but > 100, ADD 14
If score is = to 98.00 thru 100, ADD 1