Forum Discussion

Don_101's avatar
Don_101
Copper Contributor
Jan 07, 2019

Add two formulas to one cell?

Hi,

I have created a formula which changes the colour of a cell depending on certain inputs on the worksheet. However, I need the cell to change colour from red to green and include the phrase "complete". 

Is there a simple solution to the above issue?

The formula I use is: =IF(D5>0,IF(ISBLANK(I5),"Red","Green"),"white")

 

Thank you

excel
Formulas and Functions

9 Replies

  • steerr's avatar
    steerr
    Copper Contributor
    Feb 10, 2024
    Don_101 -- Yes, as Hans Vogelaar indicated, I suspect no one answered because there was insufficient information -- such as how "completion" relates to other inputs (room type?), and how your formula (which will currently evaluate to the WORDS "red", "green" or "white" -- not to colors as shown) was working for you.

    I *THINK* maybe what you need is to combine an "IF" test for whatever counts as completion (with the text "Complete" as the value-if-true) with conditional formatting, which would assign a green background if the cell value is "Complete". First you would set a value for the target cell that makes the cell either say "Complete" or stay blank, i.e =IF([some test], "Complete", ""). Then you would go to conditional formatting and choose "format cells that contain" (or something similar depending on your version of Excel) and set the test as 'equals "Complete" ', and set the format when true to a green cell background.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 10, 2024

      LanaMaria 

      If you have a similar problem, please provide detailed information about your setup, and if possible, attach a small sample workbook without sensitive information. Alternatively, upload it to for example Google Drive, OneDrive or Dropbox and post a link to the uploaded file in a reply.

      • LanaMaria's avatar
        LanaMaria
        Copper Contributor
        Feb 10, 2024

        Here goes.... I have no idea what I am doing but somehow continue to figure it out lol!! Here and YouTube are invaluable!!

         

        Here is what I need - 

         

        In cell Z8 is the total hours worked. In Z9 I would like it to reflect total break time. Every 5 hours I deduct 0.5.

         

        0-4 hrs worked - 0

        5-9hrs - 0.5hrs

        10-14hrs - 1hr

        15-19hrs worked - 1.5hrs

        20-24hrs worked - 2hrs

        25-29hrs worked - 2.5hrs

        30-34hrs worked - 3hrs

         

        Does that make sense? Basically I need to show the total scheduled AND total break times  - then cell AA8 would be =Z8-Z9.

         

        https://docs.google.com/spreadsheets/d/1vjpFhJ3e4NonjMReD4CCAowMmu3RioXP/edit?usp=share_link&ouid=100123223718955834672&rtpof=true&sd=true

         

        HansVogelaar 

Resources