cell conditional formatting to change when certain cells are filled

[mordy88]

Hi there,

I have a weird question guys, I've attached a workbook. Basically lets say i work 6 hours a day per week. 6hours * 5 days = 30 hours right...

so i have conditional formatting 6< hours a day it turns green
6< turns red
total hours at the end calculates based on the 5 days so 30+ it turns green

Heres my question
IF I'm in the middle of the week so based on my example in the practice book I've attached by wednesday I've put 3 days in therefor i would have to be at 18 to be on track... how do i set the total hours to pickup on the thursday and friday not being worked yet (i've inserted 0, I'm hoping an =if 0 ignore blah blah function is possible lol)
if i had worked 7 + 5 + 11 I'm well over so i want the 23 to turn green based on the days worked.

Is there a way that anyone knows of to auto pickup on that thurday and friday have not been worked yet.

Much appreciated

Cheers
Jeff

[mordy88]

Monday Tues Wed Thurs Fri Week
7 3 10 8 4 32

Monday Tues Wed Thurs Fri Week
7 5 11 0 0 23



6 > Green
6 < Red

my attachment won't upload

[mordy88]

I've attached the a sheet showing what it should look like for the most part.
Now because I've only input wednesday i want the total for the week to know thursday and friday are sitting at 0 calculate on a 3 day week, it should be green not red. if i input a number on thursday as long as its 6*4 or higher it should be green once i input friday same thing 6*5 or higher green.

I really hope this makes sense

[Sonia67]

Hi Jeff,

Please find attached spreadsheet.

This would be one way of doing it. I have copied the sheet, Sheet 1 shows the columns I have added to allow for formulas. And sheet 2 shows it working with the extra columns hidden.

Don't know if this is suitable for what you want.

Regards,

Sonia

[bebo021999]

Use AVERAGEIF:

First, format F3 as red

Second apply this formula in "Rule Type: Use a formula" / Edit the rules...

=AVERAGEIF(A3:E3,">0",A3:E3)>=6

set format to Green.

[mordy88]

sorry peeps... this is hard to explain
lets say f3 is red and i work monday and its a 8 hour day i put in. as long as i work over 6 hours i want the monday cell (a3) to turn green which it is, thats not the problem.
i want f3 to know one day has been worked and if its greater than 6 i want f3 to turn red
so for example: I've attached a spreadsheet i hope this explains

f3 basically need to know what day of the week it is (if i input 0 in the d3 or e3 can it know and than average the non 0 days to know its tuesday or wednesday based on whats there)

[samba_ravi]

use following formulas in G4 in conditional format

for Red
=$G4<COUNTIF($A4:$E4,">0")*6
for Green
=$G4>=COUNTIF($A4:$E4,">0")*6

[Sonia67]

Hi Jeff,

I have set up an example spreadsheet of what I think you could do, on the Example worksheet of your spreadsheet.

The conditional formatting is applied to the "On Target" column.
I have only applied text colour to the hrs worked each day - easier on the eyes.
The table at the top is used to determine Yes or No in the "On Target" column.
Change one of the 6's to 5 in the 2nd row, you will see that the value changes to No.

Hope this is suitable for what you want.

Regards,

Sonia

[Sonia67]

Hi Jeff,

Just tried out an idea and it seems to be working.

I have got the conditional formatting working for the Total Hrs column, again you can try it out by changing one of the 6's to 5.

I have then set a print area, so that when you print the spreadsheet, it will only print the columns, as in your example.

Regards,

Sonia

[mordy88]

WOW you guys are awesome i think i have it!!!
Thank you!!!

[mordy88]

I've lied, im not 100% yet sorry
nflsales what you said works exactly what i want... except... what if they are not in a row...
I've attached another sheet.. i had to put this =$AF3<COUNTIF(B3:AA3,">0")*6 but the problem is i don't want b3 through aa3 i want select cells in that formula. so for instance AF3 is to count b3,g3,l3,q3,v3,aa3 but i get an error message when i put that "to many arguments were entered for this function"
anyone know how to get around this... countif doesn't seem to allow anything but a range of cells.

[bebo021999]

Maybe this helps:

=$AF3<(COUNTIFS($B$2:$AA$2,"Call",B3:AA3,">0")*6)

[mordy88]

Excellent!! thank you so much ill let you guys know on my next issue hehe

[mordy88]

so any ideas why this wouldn't work on an excel sheet i have at work. different version of excel..
This formula: =$AF3<(COUNTIFS($B$2:$AA$2,"Call",B3:AA3,">0")*6) works perfectly on my excel sheet here at home... the format is pretty well the exact same at work but for some reason the cells are white once i save it even though one red and ones green...
there is a slight difference in the conditional formatting setup but i don't think the version of excel would cancel out a function "countif" would it?

[bebo021999]

Sorry to hear it didnot work. Try to copy that formula and paste into any cell in worksheet to check if it works.