Results 1 to 5 of 5

Thread: Conditional Formatting based on a criteria

  1. #1
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4

    Conditional Formatting based on a criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a row of values and a Standard Deviation calculation at the end of each row. I have a cell on the same sheet with an SD number in.

    I wish to colour the cell in the row which has the highest and lowest number in IF the SD of that row is below the value of the cell with the SD number in.

    I have tried varies formulas with conditional formatting and have not managed this yet.

    Any help gratefully received. Using MAC Office Excel Ver 16.4. Huge thanks.

    Best wishes,


    Stephen

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,808
    Articles
    0
    Excel Version
    O365
    I have setup data in A2:N15, sd in O2:O15, the SD threshold in a named cell SD. Adjust these formulae to your actual data

    =AND(A2=MIN($A2:$N2),$O2<SD)
    =AND(A2=MAX($A2:$N2),$O2<SD)

  3. #3
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Dear Bob,

    Brilliant - huge thanks and it works - thank you so much.

    One last question if I may - I have just noticed that a number of my rows have 0 in and I want to avoid the zero being picked up for the MIN - what can I weave into the formula =AND(A2=MIN($A2:$N2),$O2<SD) to overcome this please ?

    Thank you once again for the great help - much appreciated.

    Best wishes,


    Stephen

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,808
    Articles
    0
    Excel Version
    O365
    This should sort it for you

    =AND(A2=MIN(IF($A2:$N2=0,99^99,$A2:$N2)),$O2<SD)

  5. #5
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Many thanks Bob - that works a treat.

    Huge thanks for your help and stay safe during these troubled times !!

Posting Permissions

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