Average based on Value from a different Cell but in multiple columns

[fotodude]

Hi Guys,
I need some help. I'm trying to figure out to combine multiple formulas in one.
I have a sheet where I look in column b for a value and want to calculate the average of the % in column D. So good so far but now I want to add the same thing for when I look in column F and calculate the average % in column H and so on. It would be nice if I could create that in one formula. Right now I'm doing

=AVERAGEIF(B:B,"M",D:D) then another one =AVERAGEIF(F:F,"M",H:H) and another one =AVERAGEIF(J:J,"M",L:L) and then =AVERAGE(P2,R2,T2)

Attached please find my sample file.
Every help is appreciated.
Thanks already,
Martin

[CAntosh]

Try the following formula, which should be array-entered (Ctrl + Shift + Enter instead of Enter) in U2:

=AVERAGE(IF($B$2:$J$32=LEFT(O2,1),$D$2:$L$32))

You can fill down for Yanca and Zoey. Take a look at the attachment to see if it'll do the trick:

[fotodude]

That's exactly what I needed, thank you so much.
Maritn

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[CAntosh]

Glad to help, good luck!

[fotodude]

Now I got to another problem, Is it possible to to do multiple selection?
like I have
=SUM(IF($B$3:$V$69=LEFT(AA4,1),$C$3:$W$69)) but what if I only need $B$3:$V$35 and $B$40:$V$69 and it should not count the entries from $B$36:$V$39

Thanks,
Martin

[CAntosh]

The easy approach would be to simply do it twice, so =SUM(IF(B3:V35...) + SUM(IF(B40:V69...)

The alternative would be to build an AND term into your IF clause to eliminate the desired rows, which is doable, but the result might be a bit more complex to adapt when needed. If you only have two separate chunks of data to consider, adding separate SUM clauses would be my advice. If you decide to try the second approach and need some help, let me know.

[fotodude]

I just tried the + and it works fine for SUM but average only works if in both areas the value is available.
Is there a way to work around?
Thanks,
Martin

[CAntosh]

AVERAGE is just SUM/COUNT, so you could just expand on the idea in post #7 and use:

(SUM(RangeA) + SUM(RangeB))/(COUNTA(RangeA)+COUNTA(RangeB))

Or you could develop an AVERAGE(IF... clause similar to the one in post #2 that would exclude the undesired rows. If you get stuck, it would be helpful to see another sample workbook with some expected values.

[fotodude]

Somehow I couldn't figure it out Attached please find my latest file.
I want to have one for the whole October which works bc all name show up and one for each week in October and then I want one for each week where I get into the problem.
Thanks,
Martin

[CAntosh]

Take a look at the attachment to see if it's working as desired. I used the following (array-entered) in AC15:

=IFERROR((SUM(IF($B$4:$V$10=LEFT($AA15,1),D$4:X$10))+SUM(IF($B$39:$V$45=LEFT($AA15,1),D$39:X$45)))/(COUNT(IF($B$4:$V$10=LEFT($AA15,1),D$4:X$10))+COUNT(IF($B$39:$V$45=LEFT($AA15,1),D$39:X$45))),"No Entries")

Fill it down for the other names and right for the late 30 M %. For week 41, same thing, different range. In AC26:

=IFERROR((SUM(IF($B$11:$V$17=LEFT($AA15,1),D$11:X$17))+SUM(IF($B$46:$V$52=LEFT($AA15,1),D$46:X$52)))/(COUNT(IF($B$11:$V$17=LEFT($AA15,1),D$11:X$17))+COUNT(IF($B$46:$V$52=LEFT($AA15,1),D$46:X$52))),"No Entries")

Is that what you were after?

NOTE: I don't know what you're actually trying to measure, but it's worth noting that the weekly average you're getting is the average of the employees' daily percentages. Hopefully that's what you need? That result will not, in most cases, equal their weekly average. For instance, if I appear twice in a week:

- the first time I log 2 orders with a 50% "on time" rate
- the second time a log 100 orders with a 90% "on time" rate

The average of my daily percentages will have me at 70% for the week (avg of 50 and 90), while my actual weekly average is 91 out of 102, or 89%. The current formula returns the 70% value. We can come up with a different formula if you actually need the 89% figure, but it'll be a bit more complicated.

[fotodude]

Good point I actually forgot to mention I would need the weighted average. Any I idea if this is even possible?
Thanks,
Martin

[CAntosh]

For weighted averages, we should use SUMPRODUCT. For the monthly on time%, try the following in AC4:

=SUMPRODUCT(--($B$3:$V$68=LEFT($AA4,1)),D$3:X$68,$C$3:$W$68)/$AB4

Fill down for the other names and fill right to get the 'late 30M' percentages.

For the weeklies, I think it'd be wise to build in a bit more flexibility with your dates, rather than having to contend with your split sections. I created a date range in AB13 and AD13 where you can enter the start and end dates for your week. I used the following (array-entered) in AB15 and filled down to get the total:

=SUM(IF(($B$3:$V$68=LEFT(AA15,1))*($A$3:$A$68>=$AB$13)*($A$3:$A$68<=$AD$13),$C$3:$W$68))

For the percentages, the formula in AC15 is:

=IFERROR(SUMPRODUCT(($B$3:$V$68=LEFT($AA15,1))*($A$3:$A$68>=$AB$13)*($A$3:$A$68<=$AD$13),D$3:X$68,$C$3:$W$68)/$AB15,"No Entries")

Fill down and right for the others. Repeat the process for the next week (and any others). The dates in AB13 and AD13 act as a search range, so they don't have to span a week. You can set any begin and end dates and the data should respond accordingly. This should give you more control over what data you can grab. It will also allow you to change the date range you're looking at without having to tinker with the formula every time.

The values I've spot-checked seem to be holding up, but take a look at the attachment to see if it'll do for you:

[fotodude]

Hi CAntosh,
Absolutely blown away. This is just awesome. Thank you sooooo much for all your help and effort you put into this.
Thanks again,
Martin

[CAntosh]

My pleasure, it helped fill a slow work week! Good luck!