If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Greater than formulas with conditional formatting
Ahhh!!! About to pull my hair out! Hopefully someone out there can
help save my hair and my sanity by giving me some excel help! :-) I have roughly 17 cells that all contain sums calculated from other cells. I would like to use conditional formatting (unless there is a better suggestion) to highlight the highest sum. I've tried writting a formula within the conditional formatting that would do this but can't figure out how to write the formula that states if cell A3 is greater than A4, A5, A6, and A7. I've tried it several different ways but I keep getting an error message that says that I can't use "unions, intersections, or array constants for Conditional Formatting criteria." Any suggestions on how to write the formula? I also ideally would like to rank the top 3 sums and highlight them different colors but didn't see how that would be possible since I couldn't even get the top one figured out! I can't simply rank the cells because the sums are dynamic and will change depending on other inputs. I appreciate any help! Thanks! Jamie |
#2
|
|||
|
|||
Greater than formulas with conditional formatting
Select the cells that you want to format (cells A3:A20 in this example)
Choose FormatConditional Formatting From the first dropdown, choose Formula Is In the text box, type a formula that refers to the active cell, e.g: =A3=LARGE($A$3:$A$20,1) Click the Format button, and select the formatting for the highest sum Click OK, then click Add Under Condition 2, from the first dropdown, choose Formula Is In the text box, type: =A3=LARGE($A$3:$A$20,2) Click the Format button, and format the second highest sum Click OK, then click Add Under Condition 3, from the first dropdown, choose Formula Is In the text box, type: =A3=LARGE($A$3:$A$20,3) Click the Format button, and format the third highest sum Click OK, then click OK Jamie wrote: Ahhh!!! About to pull my hair out! Hopefully someone out there can help save my hair and my sanity by giving me some excel help! :-) I have roughly 17 cells that all contain sums calculated from other cells. I would like to use conditional formatting (unless there is a better suggestion) to highlight the highest sum. I've tried writting a formula within the conditional formatting that would do this but can't figure out how to write the formula that states if cell A3 is greater than A4, A5, A6, and A7. I've tried it several different ways but I keep getting an error message that says that I can't use "unions, intersections, or array constants for Conditional Formatting criteria." Any suggestions on how to write the formula? I also ideally would like to rank the top 3 sums and highlight them different colors but didn't see how that would be possible since I couldn't even get the top one figured out! I can't simply rank the cells because the sums are dynamic and will change depending on other inputs. I appreciate any help! Thanks! Jamie -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|