+ Reply to Thread
Results 1 to 4 of 4

Excel colour scales

  1. #1
    Registered User
    Join Date
    01-26-2020
    Location
    Vienna, Austria
    MS-Off Ver
    2010
    Posts
    2

    Angry Excel colour scales

    I am having nerve wrecking issues with Excel right now. I want to colour a cell, depending on the number in it, using a conditional format. I want three different Colour options.

    So I wrote in the Minimum one: =IF($O$98<=1,5;1;0)

    in the middle one: =IF(AND($O$98>1,5;$O$98<=2,5)=1;1;0)

    and in the maximum one: =IF($O$98>2,5;1;0)

    If I write These three Formulars outside if the conditional format, the work perfectly. However, having them in the format result in the cell alwys being red (the Colour for the highest Option), no matter what I type in.
    Except when I write 0 in the cell, then it gets the Colour from the lowest one.

    I have 2010 and have tried everything, changing the Formulars, not using the if Formular at all etc.

    I cannot understand how at least this Version of Excel has a built in function for such cases but can only give the cell Symbols this way, not just colouring it.

    Anyway, help would be greatly appreciated.

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Excel colour scales

    There could be 2 conditional because 3d is default
    =$O$98<=1.5 and
    =$O$98<=2.5
    but you should use stop options and right arrangement
    Attached Files Attached Files

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Excel colour scales

    If you want the cell to be red/yellow/green then use the 'Use a formula to determine which cells to format'. You can then use the formula you have to colour the cell.

    The color scale set if not working for you as the three values all result in 1 or 0.
    Those formula for lower/mid/high are the resulting values to be used. Your formula result in a confusing set of numbers depending on contents of O98. if the value in O98 was 2.6 then the values being used for color scale would be lower=0, mid=0, high=1. And the actual value of 2.6 does not fix in the range
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    01-26-2020
    Location
    Vienna, Austria
    MS-Off Ver
    2010
    Posts
    2

    Re: Excel colour scales

    Thank you very much! That solved my Problem ^^.

    It's just super inconvenient because one has to make 3 rules for this one coloured cell, even tho there is an extra format for such an effect, but which only can give you Symbols.
    I don't understand why this is done like that, I hope it has been changed in more recent versions

+ 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. Replies: 3
    Last Post: 07-18-2019, 11:16 AM
  2. Conditional Formating, colour scales
    By Martinb86 in forum Excel General
    Replies: 1
    Last Post: 02-15-2017, 03:54 AM
  3. editing code to change colour scales.
    By mattianperry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-18-2010, 05:46 AM
  4. Conditional Formatting - Colour Scales
    By gruf1968 in forum Excel General
    Replies: 17
    Last Post: 10-15-2010, 07:28 AM
  5. colour scales conditional formatting
    By mattianperry in forum Excel General
    Replies: 1
    Last Post: 10-15-2010, 06:43 AM
  6. TIP: Useful use of colour scales in Excel 2007
    By squiggler47 in forum Tips and Tutorials
    Replies: 0
    Last Post: 05-09-2009, 10:06 PM
  7. Colour Scales in excel 2007
    By iainiow in forum Excel General
    Replies: 0
    Last Post: 12-09-2008, 11:25 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