highlight the cell based on the range for a given value

[Narnimar]

I have a table for fee selector like this. I need to highlight the fees cell
based on the from - to range for a given value, say 65000.00

FROM TO FEES
£1.00 £2,737.00 £41.00
£2,738.00 £8,212.00 £82.00
£8,213.00 £16,424.00 £164.00
£16,425.00 £24,637.00 £218.00
£24,638.00 £41,062.00 £274.00
£41,063.00 £68,436.00 £356.00
£68,437.00 £136,873.00 £411.00
£136,874.00 £273,747.00 £547.00
£273,748.00 £547,495.00 £821.00
£547,496.00 ABOVE 0.20%

How to do it in conditional formula?

 

[Debra Dalgleish]

Select the cells that contain the fees, in this example cells C2:C11,
with cell C2 as the active cell
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:
=AND($A2<=$E$1,OR($A3>=$E$1,$A3=""))
where cell E1 is the cell that contains the 65000 value
Click the Format button, and select a highlight colour on the Patterns tab.
Click OK twice, to close the dialog boxes.

 

[Narnimar]

Yes it works great. Thanks.
Can I ask further question on it? I got a value at C12 which is to be summed
with only that highlighted figure. What will be the formula to get the Sum at
C13? Thanks again.

 

[Debra Dalgleish]

In cell C13, enter the formula:

=VLOOKUP($E$1,$A$2:$C$11,3)+ C12

 

[Narnimar]

Dear Debra Dalgleish,

I have tested it further and found that it will highlight nearest two fees
values C2 & C3 if E1=A3. The condition should highlight on only the cell C3
right?. No problem found when E1=22. There is a same problem if the value of
E1= A4 till A11 i.e from 2738.00 to 547496.00.
Also VLOOKUP result will not be correct if E1=547496.00 or higher because it
will return only the value equal to C12. In this case the Sum should be
C11+C12. I fact this value equal to 0.02% of A1. (means the fees for
547496.00 will be 1094.99). Thanks for review and your further effort.

 

[Debra Dalgleish]

Change the conditional formatting formula to:

=AND($A2<=$E$1,OR($A3>$E$1,$A3=""))

In cell C11, enter: =E1*0.2%

 

[Narnimar]

Merry Christmas! Debra Dalgleish,
Now the conditional highlight is perfect.
The fees indicated in C11 can't be changed for presentation reasons. The
figure in C13 will give wrong impression. I should accomplish the sum at C13
using some other logical test. Thanks.

 

[Debra Dalgleish]

In cell C13, enter the formula:
=IF(E1>=A11,E1*C11,VLOOKUP(E1,$A$2:$C$11,3))+C12