|
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? |
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 |
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 |
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 |
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 |
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 |
highlight the cell based on the range for a given value
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" wrote: 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 |
highlight the cell based on the range for a given value
In cell C13, enter the formula:
=IF(E1=A11,E1*C11,VLOOKUP(E1,$A$2:$C$11,3))+C12 Narnimar wrote: 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" wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 04:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com