Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlight the cell based on the range for a given value
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlight the cell based on the range for a given value
Select the cells that contain the fees, in this example cells C2:C11,
with cell C2 as the active cell Choose FormatConditional 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 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlight the cell based on the range for a given value
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" wrote: Select the cells that contain the fees, in this example cells C2:C11, with cell C2 as the active cell Choose FormatConditional 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 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlight the cell based on the range for a given value
In cell C13, enter the formula:
=VLOOKUP($E$1,$A$2:$C$11,3)+ C12 Narnimar wrote: 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" wrote: Select the cells that contain the fees, in this example cells C2:C11, with cell C2 as the active cell Choose FormatConditional 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 wrote: 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlight the cell based on the range for a given value
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" wrote: In cell C13, enter the formula: =VLOOKUP($E$1,$A$2:$C$11,3)+ C12 Narnimar wrote: 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" wrote: Select the cells that contain the fees, in this example cells C2:C11, with cell C2 as the active cell Choose FormatConditional 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 wrote: 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlight the cell based on the range for a given value
Change the conditional formatting formula to:
=AND($A2<=$E$1,OR($A3$E$1,$A3="")) In cell C11, enter: =E1*0.2% Narnimar wrote: 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" wrote: In cell C13, enter the formula: =VLOOKUP($E$1,$A$2:$C$11,3)+ C12 Narnimar wrote: 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" wrote: Select the cells that contain the fees, in this example cells C2:C11, with cell C2 as the active cell Choose FormatConditional 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 wrote: 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
VLOOKUP - unable to highlight cell range | Excel Worksheet Functions | |||
Can't highlight a single cell. Creates a range when I move curso | Excel Worksheet Functions | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) | |||
highlight range if cell contains desired data | New Users to Excel |