How to make conditional formatting exclude blank cells
Our take
Making a working hours chart but I'm not very well versed in many Excel functions so I've been consulting google results for most, but I don't understand why conditional formatting is marking blank cells green for value >0. The detailed issue is as follows:
I have columns for for date (C), start time (D), end time (E), and the following (using row 5 for example):
Total time F5 =IF(D5;E5-D5;"") Number format: Time
Overtime: G5 =IF(OR(F40="";TIME(8;;)="");"";(F40-TIME(8;;))*24) Number format: General
I have set up conditional formatting in column G to color everything that is below 0 red, everything above 0 green, but all the blank cells with above formula applied are colored green too. Cells that equal 0 are left without color, as I intended.
My hope is that blank cells also remain white until they recieve a value. Is there a way to achieve that?
I appreciate all the help!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Blank cells are being summed as a value greater than five using a SUM(IF functionHi! I have a large dataset of 7-day schedules that I am summing to calculate breaks. The function is setup like this: =SUM(IF(CELL1>5, 0.5, 0)), (IF(CELL2>5, 0.5, 0)) and so on until all seven days are tabulated. Because a full time person would only work 5 days a week, at least 2 days are blank per row, but we're open all 7 days and I want to sum the entire spreadsheet, so I have to count all 7 days. A full time person should have 2.5 hours per week. For some reason the function is mostly working, but about a third of the results are larger than they should be because it's counting some blank cells as greater than 5. For example, a part time person working only 3 days should have 1.5 break hours, but the function returns 3.5 because it is counting all the blank cells as containing a value > 5. What's really strange is it is only doing this some of the time. Every row has at least two blank cells, but only about a third of the sums are wrong. I can't figure out why. The columns are all formatted as a number. The value does update correctly if I manually enter a "0" in the blank cells, but this is a very large dataset and that would take forever. Thoughts? submitted by /u/andylefunk [link] [comments]
- How to remove blank cells as values in a ladderI am making a ladder for a PE class tournament, and the wins and losses columns are done with a =if(B5<C5,1,0) to give points or not based on which value is larger, but the draws are giving me strife. My current formula is =IF(B5=C5,1)+IF(B11=C11,1)+IF(F11=G11,1)+IF(F17=G17,1)+IF(B23=C23,1)+IF(B29=C29,1) to represent all the games the students will play, and if the soccer match ends in a draw, it adds 1 to the draw column. But when all of the cells are blank, the cell tracking draws has a 6 in it because the blank spaces are the same value. Is there a way to change my formula to exclude cell if the cells are blank? submitted by /u/Powerful-Fox4612 [link] [comments]
- Conditional Formatting within a date rangeI am building a heat map for missing timecard data by weeks. The weeks missing data are in column C->Z. Cell C2 is Jan 2nd, D2 is January 9th, etc I am trying to apply conditional formatting to the data in cells C3-Z3. But only if the date in row 2 falls within a separate date range in columns A/B. For example if someone was scheduled to work only in February, cell A3 would be Feb 1st and cell B3 would be Feb 28th. I want apply conditional formatting for the C->Z data to the right for February but not Jan/March/etc. Is it possible to do that? submitted by /u/Anneisabitch [link] [comments]