Conditional Formatting and IF Statements

[q1911]

Hey Folks:

2 questions:


Multiple IF Statements

• Background: Calculating flowthrough, returning a score (0%, 25%, 50%, 75% or 100%) based on whether sales (F5) are positive or negative)

• End Goal
  • If Cell F5 is > 100%, perform these statements: IF(L5>75%,100%,IF(L5>70%,75%,IF(L5>65%,50%,IF(L5>60%,25%,IF(L5<60%,0%,)))))))
  • If Cell F5 is < 100%, perform these statements: IF(L5>35%,100%,IF(L5>30%,75%,IF(L5>25%,50%,IF(L5>20%,25%,IF(L5<20%,0%,)))))))

• I have tried a few different ways to no avail, here is what I current have:

=IF(AND(F5>100%,(IF(L5>75%,100%,IF(L5>70%,75%,IF(L5>65%,50%,IF(L5>60%,25%,IF(L5<60%,0%,))))))),(IF(L5>35%,100%,IF(L5>30%,75%,IF(L5>25%,50%,IF(L5>20%,25%,IF(L5<20%,0%,)))))))


Conditional Formatting

• End Goal
  • Fill cells H5 and H6 (the same color depending on what's on H6)
  • I can get this to work in the two cells above, but can not copy and paste the conditional formatting into cells below. When I remove the $ from the formulas, cell H6 no longer follows the conditions.
  • I currently have to paste the formatting and manual change the H6 to H8 if I want to apply it to H7 and H8.

Whereas:

• 0% = Red
• 25% = Yellow
• 50% = Green
• 75% = Gold (orange)
• 100% = Platinum (Grey)

TIA

 

[NiMip]

I think this is the formula you're after. I've taken out the AND (I couldn't see any need for it, given what you've asked the formula to do) and removed a few brackets. I should note that the the second of your "End Goal" dot points says "If Cell F5 is < 100%". Technically the formula does F5<=100% for the second part... I'm assuming that's ok.

=IF(F5>100%,IF(L5>75%,100%,IF(L5>70%,75%,IF(L5>65%,50%,IF(L5>60%,25%,IF(L5<60%,0%,))))),IF(L5>35%,100%,IF(L5>30%,75%,IF(L5>25%,50%,IF(L5>20%,25%,IF(L5<20%,0%,))))))

Personally I don't love nested IF statements, so I'd probably be using a lookup table or something instead, with defined % values in one columns, desired results in another.

For the conditional formatting, I think you're running into trouble because you want two cells, H5 and H6, to be conditional on the result in one cell, H6, and you're asking it to hold the reference for one cell but move it for the other... you won't be able to just copy and paste the one conditional formatting formula. I think you'll have to set up two conditional formatting rules; one for H5, the other for H6, both within dynamic formulas (without $ signs) but both referencing H6. I think they should copy down alright then.