I am trying to do a formula for one cell in the same row where I need the average of specific cells in the same row across different columns if another cell has certain text in it, and depending on what the text is in that cell, what the value will be, either an average or a solid value.
In my spreadsheet, there are 5 people, each has the same categories under their names, and each row represents a different day of the week. For what I need, I will be using Row 16 (Sat 9/25) for my examples. I would like to have one formula that incorporates both conditions to either average or enter a solid value. The formula would be entered in the cell under everyone’s “U” column for each row.
For each person in Row 16, if there is text under their column labeled “KEY”, if it says “RDO”, then I simply need the column labeled “U” to equal 8.00, under their name. Using the spreadsheet, Nick has “RDO”(O16) under his “KEY” (O15), so I need his “U” (N16) to equal 8.00.
Also, for each person, in Row 16, if there is text under the column labeled “KEY”, and it says any of the following: “LV”, “SC”, “REF”, or “DT”, then I need the cell in Row 16, the column labeled “U” to equal the average of the cells in Row 16, under the columns labeled “OT W” if the values are greater than 0.17 for all the workers listed (5 total). If it is blank, I need it to ignore it. Using the spreadsheet, Andy has “LV” (H16), and Stephanie has “REF” (AC16) under their “KEY” columns. In the “U” column, Row 16, I need the formula to average anyone that has a value greater than 0.17 in column “OT W”. Looking at everyone’s “OT W” in Row 16, that would leave Jessica for 2.68 and Jeff for 3.01. Add those for 5.69 divided by 2 because only two of the 5 worked, so the average is 2.845, round up to 2.85 is what I need to see in both Andy’s and Stephanie’s “U” column, Row 16. So Andy’s G16 and Stephanie’s AB16 would have the same formula, assuming I can have the “KEY” column be different things and get the same average for that day.
I’ve tried using formulas for AVERAGE, AVERAGEIF, and AVERAGEIFS, but because I have more than one condition, and the cells that I want to have averaged are not “together”, I keep getting either “#VALUE!” errors, or it tells me there are too many arguments even if I make sure all the parentheses are closed or there is a problem with this formula (most common).
I am not familiar with macros. I did start looking at how to do those, but currently can not take the time to read up on it, and it seems pretty complex, but if that’s what is needed, I would need lots of help with that. I did try doing one that I found on another site, but I could not get it to work at all.
Is it even possible to do what I’m trying to do? Any direction or help would be much appreciated.
In my spreadsheet, there are 5 people, each has the same categories under their names, and each row represents a different day of the week. For what I need, I will be using Row 16 (Sat 9/25) for my examples. I would like to have one formula that incorporates both conditions to either average or enter a solid value. The formula would be entered in the cell under everyone’s “U” column for each row.
For each person in Row 16, if there is text under their column labeled “KEY”, if it says “RDO”, then I simply need the column labeled “U” to equal 8.00, under their name. Using the spreadsheet, Nick has “RDO”(O16) under his “KEY” (O15), so I need his “U” (N16) to equal 8.00.
Also, for each person, in Row 16, if there is text under the column labeled “KEY”, and it says any of the following: “LV”, “SC”, “REF”, or “DT”, then I need the cell in Row 16, the column labeled “U” to equal the average of the cells in Row 16, under the columns labeled “OT W” if the values are greater than 0.17 for all the workers listed (5 total). If it is blank, I need it to ignore it. Using the spreadsheet, Andy has “LV” (H16), and Stephanie has “REF” (AC16) under their “KEY” columns. In the “U” column, Row 16, I need the formula to average anyone that has a value greater than 0.17 in column “OT W”. Looking at everyone’s “OT W” in Row 16, that would leave Jessica for 2.68 and Jeff for 3.01. Add those for 5.69 divided by 2 because only two of the 5 worked, so the average is 2.845, round up to 2.85 is what I need to see in both Andy’s and Stephanie’s “U” column, Row 16. So Andy’s G16 and Stephanie’s AB16 would have the same formula, assuming I can have the “KEY” column be different things and get the same average for that day.
I’ve tried using formulas for AVERAGE, AVERAGEIF, and AVERAGEIFS, but because I have more than one condition, and the cells that I want to have averaged are not “together”, I keep getting either “#VALUE!” errors, or it tells me there are too many arguments even if I make sure all the parentheses are closed or there is a problem with this formula (most common).
I am not familiar with macros. I did start looking at how to do those, but currently can not take the time to read up on it, and it seems pretty complex, but if that’s what is needed, I would need lots of help with that. I did try doing one that I found on another site, but I could not get it to work at all.
Is it even possible to do what I’m trying to do? Any direction or help would be much appreciated.
| OT EDIT-mrexcel.xlsx | |||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | |||
| 1 | KEY : | U - Unavailable | |||||||||||||||||||||||||||||||||||||||
| 2 | LV - | On Leave | SDO - | Scheduled Day Off, only if over 8 hr average | |||||||||||||||||||||||||||||||||||||
| 3 | SC - | Schedule Change | HOL - | Holiday Schedule, only if over 8 hr average | |||||||||||||||||||||||||||||||||||||
| 4 | MAX - | Worked max of 20 hrs already | OT W - | Overtime Worked | |||||||||||||||||||||||||||||||||||||
| 5 | REF - | Refused opportunity | O - | Opportunity | |||||||||||||||||||||||||||||||||||||
| 6 | DT - | OT done in DT (down time), did not inform supv. | DT - | Daily Total | |||||||||||||||||||||||||||||||||||||
| 7 | RDO - | Refused Day Off | TO - | Total Opportunites | |||||||||||||||||||||||||||||||||||||
| 8 | E - | Equitable | |||||||||||||||||||||||||||||||||||||||
| 9 | Y - | YELLOW | U - | Unavailable | |||||||||||||||||||||||||||||||||||||
| 10 | B - | BLUE | TA - | Total Accumulative | |||||||||||||||||||||||||||||||||||||
| 11 | G - | GREEN | |||||||||||||||||||||||||||||||||||||||
| 12 | W - | BROWN | Rte | LIST | NS DAY | TO | TE | TU | TA | Rte | LIST | NS DAY | TO | TE | TU | TA | Rte | LIST | NS DAY | TO | TE | TU | TA | Rte | LIST | NS DAY | TO | TE | TU | TA | Rte | LIST | NS DAY | TO | TE | TU | TA | ||||
| 13 | R - | RED | 232 | 12 | GREEN | 25 | 57.55 | 0.00 | 57.55 | 241 | 12 | BLUE | 27 | 38.10 | 0.00 | 38.10 | 209 | 12 | BROWN | 29 | 52.20 | 0.00 | 52.20 | T6 | 12 | YELLOW | 23 | 34.05 | 0.00 | 34.05 | 2444 | 12 | YELLOW | 30 | 82.79 | 0.00 | 82.79 | ||||
| 14 | K - | BLACK | 1 | ANDY | 2 | NICK | 3 | JESSICA | 4 | STEPHANIE | 5 | JEFF | |||||||||||||||||||||||||||||
| 15 | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | ||||||
| 16 | WEEK 1 | SAT | 9/25 | B | LV | 0 | 0.00 | 0.00 | RDO | 0 | 0.00 | 0.00 | 2.68 | 1 | 2.68 | 2.68 | LV | 0 | 0.00 | 0.00 | 3.01 | 1 | 3.01 | 3.01 | |||||||||||||||||
| 17 | SUN | 9/26 | B | 0 | 0.00 | 0.00 | 0 | 0.00 | 0.00 | 0 | 0.00 | 2.68 | 0 | 0.00 | 0.00 | 0 | 0.00 | 3.01 | |||||||||||||||||||||||
| 18 | MON | 9/27 | G | 8.00 | 1 | 8.00 | 8.00 | 2.42 | 1 | 2.42 | 2.42 | 0.84 | 1 | 0.84 | 3.52 | 0 | 0.00 | 0.00 | 0.79 | 1 | 0.79 | 3.80 | |||||||||||||||||||
| 19 | TUE | 9/28 | W | 0 | 0.00 | 8.00 | 0.12 | 1 | 0.12 | 2.54 | 0 | 0.00 | 3.52 | 0 | 0.00 | 0.00 | 0 | 0.00 | 3.80 | ||||||||||||||||||||||
| 20 | WED | 9/29 | R | 0 | 0.00 | 8.00 | 0.18 | 1 | 0.18 | 2.72 | 0 | 0.00 | 3.52 | 0.18 | 1 | 0.18 | 0.18 | 0.30 | 1 | 0.30 | 4.10 | ||||||||||||||||||||
| 21 | THR | 9/30 | K | 0 | 0.00 | 8.00 | 0.27 | 1 | 0.27 | 2.99 | 0 | 0.00 | 3.52 | 0 | 0.00 | 0.18 | 0 | 0.00 | 4.10 | ||||||||||||||||||||||
| 22 | FRI | 10/1 | Y | 0.80 | 1 | 0.80 | 8.80 | 0 | 0.00 | 2.99 | 1.10 | 1 | 1.10 | 4.62 | 0 | 0.00 | 0.18 | 8.00 | 0.14 | 1 | 8.14 | 12.24 | |||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H13,AJ13,AC13,V13,O13 | H13 | =SUMIF(I16:I113,"<>") |
| I13,AK13,AD13,W13,P13 | I13 | =SUMIF(E16:F113,"<>") |
| J13,AL13,AE13,X13,Q13 | J13 | =SUMIF(G16:G113,"<>") |
| K13,AM13,AF13,Y13,R13 | K13 | =K113 |
| I16:I22,AK16:AK22,AD16:AD22,W16:W22,P16:P22 | I16 | =VALUE(IF(COUNTIF(E16:F16,"<>"),"1",)) |
| J16:J22,AL16:AL22,AE16:AE22,X16:X22,Q16:Q22 | J16 | =E16+F16+G16 |
| K16,AM16,AF16,Y16,R16 | K16 | =J16 |
| K17:K22,AM17:AM22,AF17:AF22,Y17:Y22,R17:R22 | K17 | =K16+J17 |
| D18:D22 | D18 | =IF(D17="B","G",IF(D17="G","W",IF(D17="W","R",IF(D17="R","K",IF(D17="K","Y","B"))))) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| solver_opt | =Sheet1!$T$16 | W13, W16:X16 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A14:C14 | Expression | =$G14 | text | YES |
| A13:C13 | Expression | =$G13 | text | YES |
| A12:C12 | Expression | =$G12 | text | YES |
| A11:C11 | Expression | =$G11 | text | YES |
| A10:C10 | Expression | =$G10 | text | YES |
| A9:C9 | Expression | =$G9 | text | YES |
| S8:T8 | Expression | =$G8 | text | YES |
| S7:T7 | Expression | =$G7 | text | YES |
| S6:T6 | Expression | =$G6 | text | YES |
| AG14:AH14 | Expression | =$AI13="RED" | text | NO |
| AG14:AH14 | Expression | =$AI13="GREEN" | text | NO |
| AG14:AH14 | Expression | =$AI13="YELLOW" | text | NO |
| AG14:AH14 | Expression | =$AI13="BROWN" | text | NO |
| AG14:AH14 | Expression | =$AI13="BLUE" | text | NO |
| AG14:AH14 | Expression | =$AI13="BLACK" | text | NO |
| Z14:AA14 | Expression | =$AB13="RED" | text | NO |
| Z14:AA14 | Expression | =$AB13="GREEN" | text | NO |
| Z14:AA14 | Expression | =$AB13="YELLOW" | text | NO |
| Z14:AA14 | Expression | =$AB13="BROWN" | text | NO |
| Z14:AA14 | Expression | =$AB13="BLUE" | text | NO |
| Z14:AA14 | Expression | =$AB13="BLACK" | text | NO |
| E14:F14 | Expression | =$G13="RED" | text | NO |
| E14:F14 | Expression | =$G13="GREEN" | text | NO |
| E14:F14 | Expression | =$G13="YELLOW" | text | NO |
| E14:F14 | Expression | =$G13="BROWN" | text | NO |
| E14:F14 | Expression | =$G13="BLUE" | text | NO |
| E14:F14 | Expression | =$G13="BLACK" | text | NO |
| S14:T14 | Expression | =$U13="RED" | text | NO |
| S14:T14 | Expression | =$U13="GREEN" | text | NO |
| S14:T14 | Expression | =$U13="YELLOW" | text | NO |
| S14:T14 | Expression | =$U13="BROWN" | text | NO |
| S14:T14 | Expression | =$U13="BLUE" | text | NO |
| S14:T14 | Expression | =$U13="BLACK" | text | NO |
| E15:K15,E16:H113,J16:K113,E115:K1706 | Expression | =$G14=”BLACK” | text | NO |
| AI13 | Cell Value | contains "GREEN" | text | NO |
| AI13 | Cell Value | contains "BLUE" | text | NO |
| AI13 | Cell Value | contains "YELLOW" | text | NO |
| AI13 | Cell Value | contains "BLACK" | text | NO |
| AI13 | Cell Value | contains "RED" | text | NO |
| AI13 | Cell Value | contains "BROWN" | text | NO |
| AB13 | Cell Value | contains "GREEN" | text | NO |
| AB13 | Cell Value | contains "BLUE" | text | NO |
| AB13 | Cell Value | contains "YELLOW" | text | NO |
| AB13 | Cell Value | contains "BLACK" | text | NO |
| AB13 | Cell Value | contains "RED" | text | NO |
| AB13 | Cell Value | contains "BROWN" | text | NO |
| U13 | Cell Value | contains "GREEN" | text | NO |
| U13 | Cell Value | contains "BLUE" | text | NO |
| U13 | Cell Value | contains "YELLOW" | text | NO |
| U13 | Cell Value | contains "BLACK" | text | NO |
| U13 | Cell Value | contains "RED" | text | NO |
| U13 | Cell Value | contains "BROWN" | text | NO |
| N13 | Cell Value | contains "GREEN" | text | NO |
| N13 | Cell Value | contains "BLUE" | text | NO |
| N13 | Cell Value | contains "YELLOW" | text | NO |
| N13 | Cell Value | contains "BLACK" | text | NO |
| N13 | Cell Value | contains "RED" | text | NO |
| N13 | Cell Value | contains "BROWN" | text | NO |
| G13 | Cell Value | contains "GREEN" | text | NO |
| G13 | Cell Value | contains "BLUE" | text | NO |
| G13 | Cell Value | contains "YELLOW" | text | NO |
| G13 | Cell Value | contains "BLACK" | text | NO |
| G13 | Cell Value | contains "RED" | text | NO |
| G13 | Cell Value | contains "BROWN" | text | NO |
| L14:M14 | Expression | =$N13="BLACK" | text | YES |
| L14:M14 | Expression | =$N13="RED" | text | YES |
| L14:M14 | Expression | =$N13="BROWN" | text | YES |
| L14:M14 | Expression | =$N13="BLUE" | text | YES |
| L14:M14 | Expression | =$N13="YELLOW" | text | YES |
| L14:M14 | Expression | =$N14="GREEN" | text | YES |
| D:D | Cell Value | contains "K" | text | YES |
| D:D | Cell Value | contains "Y" | text | YES |
| D:D | Cell Value | contains "R" | text | YES |
| D:D | Cell Value | contains "W" | text | YES |
| D:D | Cell Value | contains "G" | text | YES |
| D:D | Cell Value | contains "B" | text | YES |
| D:D | Cell Value | contains "K" | text | YES |
| A16 | Expression | =$G16 | text | YES |
| J7:K7 | Expression | =$G7 | text | YES |
| J6:K6 | Expression | =$G6 | text | YES |
| J5:K5 | Expression | =$G5 | text | YES |
| J4:K4 | Expression | =$G4 | text | YES |
| J3:K3 | Expression | =$G3 | text | YES |
| J2:K2 | Expression | =$G2 | text | YES |
