+ Reply to Thread
Results 1 to 3 of 3

multi column conditional formatting

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    multi column conditional formatting

    Hi guys

    sorry to bother you, but I have been stuck on this particular formula for the last week, and I don’t seem to be progressing any further, and I am hoping you can help


    basically there are 6 columns, within these 6 columns there is various data. The data will either be a number or –. (-) is meant to show n/a, but for reporting purposes I need it to remain (-)

    what I am trying to achieve with the formula ,

    1)formula searches the multiple columns, and whatever the last number is within those column highlight that particular cell
    2) however only highlight the cell if it is higher than the average (which is also provided in the excel sheet) and this needs to be highlighted red


    hope this is self explanatory , if you have any questions please contact me
    Attached Files Attached Files
    Last edited by masond3; 11-13-2011 at 01:23 PM.

  2. #2
    Registered User
    Join Date
    08-21-2011
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: multi column conditional formatting

    Select C4:H18 & use this formula in Conditional Formatting. Make sure C4 is the active cell.

    =AND(ROWS(C$4:C4)=MATCH(9E+300,C$4:C$18),LOOKUP(9E+300,C$4:C$18)>C$23)

    See the attached.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: multi column conditional formatting

    Hi


    Thank you for your help, when checking the formula it looks like column f doesn’t seem to be working.

    I see that that the value of the cell is based on the averages which I have provided. If I take
    column c, row 4 for example which is currently -. If I type in a number higher than the average 11, eg 12 it automatically turns red (which is brilliant).

    However I only want the cell highlighted red, if it’s the last stage and over the average

    so if it take row 7, although E7 is higher than average 11, this shouldn’t be highlighted. However h7 should as it’s the last stage and its over the average

    if I take row 13, g13 currently has a 12 in it, this is higher than the average of 8, once again this shouldn’t be highlighted as there is another stage, h12 should be highlighted red as it’s the last stage and its higher than the average of 21

    row 14 ,h14 and row 15 h15 should be highlighted red as it over 21 day average

    hope this makes it a lot clearly

    also if there values already in there, will it automatically highlight a colour, or do I manually need to add the values in for the cell to be highlighted ?

    regards

    Daniel

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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