Forum Discussion
Don_101
Jan 07, 2019Copper Contributor
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
9 Replies
Sort By
- steerrCopper ContributorDon_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. - LanaMariaCopper Contributor
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.
- LanaMariaCopper Contributor
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.