Conditional Formatting and IF Statements

q1911

New Member
Joined
Apr 10, 2013
Messages
10
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)


Img.png


b3MK7m

Whole.png



TIA
b3MK7m
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top