+ Reply to Thread
Results 1 to 5 of 5

Need Help with Conditional Formatting Based on Multiple Criteria

  1. #1
    Registered User
    Join Date
    08-13-2024
    Location
    Uganda
    MS-Off Ver
    MS 13
    Posts
    1

    Need Help with Conditional Formatting Based on Multiple Criteria

    Hi everyone, I am Emmanuel Katto and I would appreciate your guidance on a specific issue I'm facing with conditional formatting.

    I have a dataset that tracks monthly sales figures for different products across several regions. The layout is as follows:

    Column A: Product Name
    Column B: Region
    Column C: Sales Figures
    Column D: Target Sales

    I want to apply conditional formatting to the Sales Figures (Column C) so that:

    If the sales figures (Column C) meet or exceed the target sales (Column D), the cell should be highlighted in green.
    If the sales figures fall below 80% of the target sales, the cell should be highlighted in red.
    If the sales figures are between 80% and 100% of the target sales, the cell should be highlighted in yellow.

    The Challenge:
    I’m not sure how to set up these multiple rules for conditional formatting. I tried using the 'Manage Rules' option, but I’m having difficulty figuring out the correct formulas to use for each condition.

    What I’ve Tried:

    I created individual conditional formatting rules but had trouble prioritizing them properly, resulting in some cells not formatting as expected.
    I attempted using formulas directly in the conditional formatting rules, such as:
    For green: =C1>=D1
    For red: =C1<0.8*D1
    For yellow: =AND(C1<0.8*D1, C1<D1)
    However, I believe I might be missing some adjustments because they don’t seem to work correctly.

    Could someone please provide a step-by-step guide on how to set up conditional formatting based on these criteria? Also, any tips on how to ensure that the rules are evaluated in the correct order would be greatly appreciated.

    Thank you in advance for your assistance!

    Best,
    Emmanuel Katto
    Last edited by AliGW; 08-13-2024 at 01:27 AM. Reason: Irrelevant part of thread title removed.

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2408 and WPS V2024(12.1.0.18543)
    Posts
    3,834

    Re: Need Help with Conditional Formatting Based on Multiple Criteria

    Hi emmanuelkatto

    Please read and refer to this link, how to upload attachments,follow mathod 2
    https://www.excelforum.com/the-water...his-forum.html

    Please add some manually calculated expected results
    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,952

    Re: Need Help with Conditional Formatting Based on Multiple Criteria

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

    Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

    The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,952

    Re: Need Help with Conditional Formatting Based on Multiple Criteria

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

    Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

    (Note: this requirement is not optional. As you're new here, I shall do it for you tjis time: https://forums.excelguru.ca/threads/...es-data.11896/)

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,779

    Re: Need Help with Conditional Formatting Based on Multiple Criteria

    @Ali,
    They aren't the same question though?
    Everyone who confuses correlation and causation ends up dead.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Based on Multiple Criteria
    By VanSummers in forum Excel General
    Replies: 0
    Last Post: 07-18-2024, 02:24 PM
  2. Conditional; Formatting based on multiple criteria
    By Muke41 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2022, 10:22 AM
  3. Conditional Formatting Based on Multiple Possible Criteria
    By Newb22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-26-2022, 09:48 PM
  4. [SOLVED] Conditional formatting based on multiple criteria
    By itsjamaal in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2022, 07:24 AM
  5. Conditional Formatting based on multiple criteria
    By sealpest_7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2021, 12:43 AM
  6. Conditional Formatting based on Multiple Criteria
    By purplexbatman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2019, 11:49 PM
  7. Replies: 4
    Last Post: 01-20-2011, 01:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1