Hello! I'm trying to create a nesting formula and I can get each one to work independently, but when I nest them altogether, some of them don't work anymore and I can't figure out why. Mainly, they are the ones where I'm trying to calculate an average if the amounts are all <=2.00. I do have separate formulas for the amounts >2.00 because those calculations depend on other factors which work both independently and in the nesting formula. I have tried changing the independent formulas to get them to work in the nest to no avail.
This is where I left off at and I'm referencing Column I because that has two of the conditions that don't work. These are the segments that don't work in the nest:
IF(OR(J4={"REF","OTDT"},AND($B$2:$P$2,"12",$B$3:$P$3,"OT W","<=2.00")),(AVERAGEIFS($B4:$P4,$B$3:$P$3,"OT W",$B4:$P4,">0.17",$B$3:$P$3,"OT W",$B4:$P4,"<=2.00")-$H4),
IF(OR(J4={"REF","OTDT"},AND($B$2:$P$2,"10",$B$3:$P$3,"OT W","<=2.00")),(AVERAGEIFS($B4:$P4,$B$3:$P$3,"OT W",$B4:$P4,">0.17",$B$3:$P$3,"OT W",$B4:$P4,"<=2.00")-$H4),
IF(OR(J4={"LV","SC"},AND($B$2:$P$2,"12",$B$3:$P$3,"OT W","<=2.00")),AVERAGEIFS($B4:$P4,$B$3:$P$3,"OT W",$B4:$P4,">0.17",$B4:$P4,"<2.01"),
IF(OR(J4={"LV","SC"},AND($B$2:$P$2,"10",$B$3:$P$3,"OT W","<=2.00")),AVERAGEIFS($B4:$P4,$B$3:$P$3,"OT W",$B4:$P4,">0.17",$B4:$P4,"<2.01"),
I have attached a shortened version of my worksheet to show the representative data. The formula will go under each Carrier's column labeled "U" (for unavailable).
Any help/guidance would be much appreciated!
Bookmarks