|
Referencing
Is there a function that I can use in the conditional formatting that will
reference the cells in another column and return a yes answer if within a set % limit? More clarification: If A3 shows 5 and B3 shows 5.1, then A3 will be highlighted. But if A3 shows 5 and B3 shows 10 then A3 will remain unhighlighted. I would like this to be done for each row. Can this be done in conditional formatting? -- Thank you! |
Referencing
Yes, you can do this with CF. You can use a an empty cell to play with CF
formulas until you get consistent TRUE/FALSE answers when copied down your sample data. This way you know it will work when put into CF since all CF formulas are TRUE/FALSE formulas. You didn't specify the percentage you wanted to tolerate, so I picked 10%. In C3 put this formula and copy down: =(MAX($A3,$B3)/MIN($A3,$B3))<1.1 If you're happy with the results, then highlight A3:B500 (or however far down you're going, don't use the whole column...bad habit.) and use that CF formula. It will adjust itself for the other rows. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Doug" wrote: Is there a function that I can use in the conditional formatting that will reference the cells in another column and return a yes answer if within a set % limit? More clarification: If A3 shows 5 and B3 shows 5.1, then A3 will be highlighted. But if A3 shows 5 and B3 shows 10 then A3 will remain unhighlighted. I would like this to be done for each row. Can this be done in conditional formatting? -- Thank you! |
Referencing
Just one problem. It highlights all the blank cells also. I only want it to
highlight cells that I have something typed into column a. All of column b has data, but only a few in column A have data. -- Thank you! "JBeaucaire" wrote: Yes, you can do this with CF. You can use a an empty cell to play with CF formulas until you get consistent TRUE/FALSE answers when copied down your sample data. This way you know it will work when put into CF since all CF formulas are TRUE/FALSE formulas. You didn't specify the percentage you wanted to tolerate, so I picked 10%. In C3 put this formula and copy down: =(MAX($A3,$B3)/MIN($A3,$B3))<1.1 If you're happy with the results, then highlight A3:B500 (or however far down you're going, don't use the whole column...bad habit.) and use that CF formula. It will adjust itself for the other rows. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Doug" wrote: Is there a function that I can use in the conditional formatting that will reference the cells in another column and return a yes answer if within a set % limit? More clarification: If A3 shows 5 and B3 shows 5.1, then A3 will be highlighted. But if A3 shows 5 and B3 shows 10 then A3 will remain unhighlighted. I would like this to be done for each row. Can this be done in conditional formatting? -- Thank you! |
Referencing
how do I keep blank cells from being highlighted? For some reason it thinks
they fit the formula in CF? -- Thank you! "JBeaucaire" wrote: Yes, you can do this with CF. You can use a an empty cell to play with CF formulas until you get consistent TRUE/FALSE answers when copied down your sample data. This way you know it will work when put into CF since all CF formulas are TRUE/FALSE formulas. You didn't specify the percentage you wanted to tolerate, so I picked 10%. In C3 put this formula and copy down: =(MAX($A3,$B3)/MIN($A3,$B3))<1.1 If you're happy with the results, then highlight A3:B500 (or however far down you're going, don't use the whole column...bad habit.) and use that CF formula. It will adjust itself for the other rows. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Doug" wrote: Is there a function that I can use in the conditional formatting that will reference the cells in another column and return a yes answer if within a set % limit? More clarification: If A3 shows 5 and B3 shows 5.1, then A3 will be highlighted. But if A3 shows 5 and B3 shows 10 then A3 will remain unhighlighted. I would like this to be done for each row. Can this be done in conditional formatting? -- Thank you! |
| All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com