formula for the average of cells across columns with multiple conditions

kckliodna

New Member
Joined
Jul 30, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.

OT EDIT-mrexcel.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1KEY :U - Unavailable
2LV -On LeaveSDO -Scheduled Day Off, only if over 8 hr average
3SC -Schedule ChangeHOL -Holiday Schedule, only if over 8 hr average
4MAX -Worked max of 20 hrs alreadyOT W -Overtime Worked
5REF -Refused opportunityO -Opportunity
6DT -OT done in DT (down time), did not inform supv.DT -Daily Total
7RDO -Refused Day OffTO -Total Opportunites
8E -Equitable
9Y -YELLOWU -Unavailable
10B -BLUETA -Total Accumulative
11G -GREEN
12W -BROWNRteLISTNS DAYTOTETUTARteLISTNS DAYTOTETUTARteLISTNS DAYTOTETUTARteLISTNS DAYTOTETUTARteLISTNS DAYTOTETUTA
13R -RED23212GREEN2557.550.0057.5524112BLUE2738.100.0038.1020912BROWN2952.200.0052.20T612YELLOW2334.050.0034.05244412YELLOW3082.790.0082.79
14K -BLACK1ANDY2NICK3JESSICA4STEPHANIE5JEFF
15SDOOT WUKeyODTTASDOOT WUKeyODTTASDOOT WUKeyODTTASDOOT WUKeyODTTASDOOT WUKeyODTTA
16WEEK 1SAT9/25BLV00.000.00RDO00.000.002.6812.682.68LV00.000.003.0113.013.01
17SUN9/26B00.000.0000.000.0000.002.6800.000.0000.003.01
18MON9/27G8.0018.008.002.4212.422.420.8410.843.5200.000.000.7910.793.80
19TUE9/28W00.008.000.1210.122.5400.003.5200.000.0000.003.80
20WED9/29R00.008.000.1810.182.7200.003.520.1810.180.180.3010.304.10
21THR9/30K00.008.000.2710.272.9900.003.5200.000.1800.004.10
22FRI10/1Y0.8010.808.8000.002.991.1011.104.6200.000.188.000.1418.1412.24
Sheet1
Cell Formulas
RangeFormula
H13,AJ13,AC13,V13,O13H13=SUMIF(I16:I113,"<>")
I13,AK13,AD13,W13,P13I13=SUMIF(E16:F113,"<>")
J13,AL13,AE13,X13,Q13J13=SUMIF(G16:G113,"<>")
K13,AM13,AF13,Y13,R13K13=K113
I16:I22,AK16:AK22,AD16:AD22,W16:W22,P16:P22I16=VALUE(IF(COUNTIF(E16:F16,"<>"),"1",))
J16:J22,AL16:AL22,AE16:AE22,X16:X22,Q16:Q22J16=E16+F16+G16
K16,AM16,AF16,Y16,R16K16=J16
K17:K22,AM17:AM22,AF17:AF22,Y17:Y22,R17:R22K17=K16+J17
D18:D22D18=IF(D17="B","G",IF(D17="G","W",IF(D17="W","R",IF(D17="R","K",IF(D17="K","Y","B")))))
Named Ranges
NameRefers ToCells
solver_opt=Sheet1!$T$16W13, W16:X16
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A14:C14Expression=$G14textYES
A13:C13Expression=$G13textYES
A12:C12Expression=$G12textYES
A11:C11Expression=$G11textYES
A10:C10Expression=$G10textYES
A9:C9Expression=$G9textYES
S8:T8Expression=$G8textYES
S7:T7Expression=$G7textYES
S6:T6Expression=$G6textYES
AG14:AH14Expression=$AI13="RED"textNO
AG14:AH14Expression=$AI13="GREEN"textNO
AG14:AH14Expression=$AI13="YELLOW"textNO
AG14:AH14Expression=$AI13="BROWN"textNO
AG14:AH14Expression=$AI13="BLUE"textNO
AG14:AH14Expression=$AI13="BLACK"textNO
Z14:AA14Expression=$AB13="RED"textNO
Z14:AA14Expression=$AB13="GREEN"textNO
Z14:AA14Expression=$AB13="YELLOW"textNO
Z14:AA14Expression=$AB13="BROWN"textNO
Z14:AA14Expression=$AB13="BLUE"textNO
Z14:AA14Expression=$AB13="BLACK"textNO
E14:F14Expression=$G13="RED"textNO
E14:F14Expression=$G13="GREEN"textNO
E14:F14Expression=$G13="YELLOW"textNO
E14:F14Expression=$G13="BROWN"textNO
E14:F14Expression=$G13="BLUE"textNO
E14:F14Expression=$G13="BLACK"textNO
S14:T14Expression=$U13="RED"textNO
S14:T14Expression=$U13="GREEN"textNO
S14:T14Expression=$U13="YELLOW"textNO
S14:T14Expression=$U13="BROWN"textNO
S14:T14Expression=$U13="BLUE"textNO
S14:T14Expression=$U13="BLACK"textNO
E15:K15,E16:H113,J16:K113,E115:K1706Expression=$G14=”BLACK”textNO
AI13Cell Valuecontains "GREEN"textNO
AI13Cell Valuecontains "BLUE"textNO
AI13Cell Valuecontains "YELLOW"textNO
AI13Cell Valuecontains "BLACK"textNO
AI13Cell Valuecontains "RED"textNO
AI13Cell Valuecontains "BROWN"textNO
AB13Cell Valuecontains "GREEN"textNO
AB13Cell Valuecontains "BLUE"textNO
AB13Cell Valuecontains "YELLOW"textNO
AB13Cell Valuecontains "BLACK"textNO
AB13Cell Valuecontains "RED"textNO
AB13Cell Valuecontains "BROWN"textNO
U13Cell Valuecontains "GREEN"textNO
U13Cell Valuecontains "BLUE"textNO
U13Cell Valuecontains "YELLOW"textNO
U13Cell Valuecontains "BLACK"textNO
U13Cell Valuecontains "RED"textNO
U13Cell Valuecontains "BROWN"textNO
N13Cell Valuecontains "GREEN"textNO
N13Cell Valuecontains "BLUE"textNO
N13Cell Valuecontains "YELLOW"textNO
N13Cell Valuecontains "BLACK"textNO
N13Cell Valuecontains "RED"textNO
N13Cell Valuecontains "BROWN"textNO
G13Cell Valuecontains "GREEN"textNO
G13Cell Valuecontains "BLUE"textNO
G13Cell Valuecontains "YELLOW"textNO
G13Cell Valuecontains "BLACK"textNO
G13Cell Valuecontains "RED"textNO
G13Cell Valuecontains "BROWN"textNO
L14:M14Expression=$N13="BLACK"textYES
L14:M14Expression=$N13="RED"textYES
L14:M14Expression=$N13="BROWN"textYES
L14:M14Expression=$N13="BLUE"textYES
L14:M14Expression=$N13="YELLOW"textYES
L14:M14Expression=$N14="GREEN"textYES
D:DCell Valuecontains "K"textYES
D:DCell Valuecontains "Y"textYES
D:DCell Valuecontains "R"textYES
D:DCell Valuecontains "W"textYES
D:DCell Valuecontains "G"textYES
D:DCell Valuecontains "B"textYES
D:DCell Valuecontains "K"textYES
A16Expression=$G16textYES
J7:K7Expression=$G7textYES
J6:K6Expression=$G6textYES
J5:K5Expression=$G5textYES
J4:K4Expression=$G4textYES
J3:K3Expression=$G3textYES
J2:K2Expression=$G2textYES
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi @kckliodna
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

In cells G16, N16, U16, AB16 and AI16 put the following formula:

Excel Formula:
=IF(H16="",0,IF(H16="RDO",8,IF(OR(H16={"LV","SC","REF","DT"}),AVERAGEIFS($E16:$AM16,$E$15:$AM$15,"OT W",$E16:$AM16,">0.17"),0)))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 2
Solution
=IF(H16="",0,IF(H16="RDO",8,IF(OR(H16={"LV","SC","REF","DT"}),AVERAGEIFS($E16:$AM16,$E$15:$AM$15,"OT W",$E16:$AM16,">0.17"),0)))
OM! - You're life a saver!! It works!! We've literally have spent almost a whole week trying to figure this out!! THANK YOU!!!
 
Upvote 0
Im glad to help you. Thanks for the feedback.

Please, you should mark the post that solved your question instead of marking your own answer as a solution. Thanks.
:)
 
Upvote 0
Im glad to help you. Thanks for the feedback.

Please, you should mark the post that solved your question instead of marking your own answer as a solution. Thanks.
:)
Sorry! New to this. I think I got it right this time. Thanks again!!!
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top