+ Reply to Thread
Results 1 to 10 of 10

Mark cell with colour with largest deviation

  1. #1
    Registered User
    Join Date
    09-15-2020
    Location
    Germany
    MS-Off Ver
    2020
    Posts
    4

    Question Mark cell with colour with largest deviation

    Hello together,

    I am currently creating an Excel list which shows the deviations from given values. However, I would like to highlight the line with the deviations in colour but only the largest deviation from the actual target value.

    Problem:
    In Cell F i have have the given values e.x. 73,6 in 6 lines
    And in cell I i have the values from me in a range of 70,7-75,6 in 6 lines.[table="width: 500, class: grid, align: left"]

    F: I:
    Given Values: Deviation:
    73,6 70,7
    73,6 71,7
    73,6 75,6
    73,6 74,6
    73,6 73,4
    73,6 78,5

    Now it should mark in a colour the line with the highest deviation from my given values (cell F).
    With conditional formatting i am only capable of given a fix range of the deviation but it's not good if i even change the given values in cell F.

    Someone has any solution here?

    Have a nice day and thank you in advance!

    Greetings,
    Daniel
    Attached Files Attached Files
    Last edited by DanielSchneider; 01-20-2021 at 09:02 AM. Reason: Adding File

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,414

    Re: Mark cell with colour with largest deviation

    Without sample file, just guess:

    Conditinal formula in cell H1:

    =H1=AGGREGATE(14,6,($H$1:$H$100)/($E$1:$E$100=E1),1)
    Quang PT

  3. #3
    Registered User
    Join Date
    09-15-2020
    Location
    Germany
    MS-Off Ver
    2020
    Posts
    4

    Re: Mark cell with colour with largest deviation

    Ah sorry - just added the file now.

  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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,442

    Re: Mark cell with colour with largest deviation

    Does the suggestion work?
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,414

    Re: Mark cell with colour with largest deviation

    =I9=AGGREGATE(14,6,($I$9:$I$100)/($H$9:$H$100=$H9),1)

    see attachment.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Mark cell with colour with largest deviation

    or:

    =(ABS($I9-$F9)=MAX(ABS($I$9:$I$18-$F$9:$F$18)))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Registered User
    Join Date
    09-15-2020
    Location
    Germany
    MS-Off Ver
    2020
    Posts
    4

    Re: Mark cell with colour with largest deviation

    Thank you it worked, is there any possibility that it does this for each unique "IQS Pos. No"? So we have for example 5 different measures "IQS Pos. No" in this Excel it is 16 and 18 but it also can be other unique numbers and it will only check for each IQS Pos. No for its own?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Mark cell with colour with largest deviation

    You didn't say who you were talking to. That's confusing when you have multiple respondents. If it was me, then:


    =ABS($I9-$F9)=AGGREGATE(14,6,ABS($I$9:$I$18-$F$9:$F$18)/(($C$9:$C$18=$C9)*($F$9:$F$18<>"")),1)

    If it was Bebo, then ignore this post!!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-15-2020
    Location
    Germany
    MS-Off Ver
    2020
    Posts
    4

    Re: Mark cell with colour with largest deviation

    oh sorry - first time in a forum. But thank you Both - that was really really helpfull!!!!

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Mark cell with colour with largest deviation

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Lookup Next Occurrence and Mark Halfway Mark with "Mark"
    By mrr2 in forum Excel General
    Replies: 4
    Last Post: 10-13-2020, 02:27 PM
  2. Change the colour of the cell value if it crosses 15 days mark
    By me_shubham2612 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-31-2019, 09:29 AM
  3. VBA for largest cell + 1 inserted below largest cell
    By MFreak in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2016, 01:53 PM
  4. Replies: 2
    Last Post: 10-28-2013, 03:52 PM
  5. [SOLVED] I cannot automatic - mark with colour a set dates
    By jpol in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 02-04-2013, 04:46 AM
  6. Replies: 20
    Last Post: 05-19-2011, 12:32 PM
  7. How to give comment mark different colour?
    By toplisek in forum Excel General
    Replies: 2
    Last Post: 03-01-2008, 06:22 AM

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