merging 3 separate index searches in to one formula

jono

New member
Joined
Dec 31, 2015
Messages
24
Reaction score
0
Points
1
Excel Version(s)
2016
Hi,

I have a spreadsheet which contains a phone system call queues, I need to find the same item for 3 potential queues each month, I can do this easily using Index formula for a single queue.

Searching Col K for the time info I'm looking for, N2 is the month name to search for, Col A is where to search for the month name, Col B is the Phone queue name col.

=IFNA(INDEX($K:$K, MATCH(1, (N2=$A:$A) * ("Queue1"=$B:$B), 0)),"0")

What I'm trying to do is combine 3 different queue names into the one formula (end result is to get the average of the 3), for testing I currently have 3 separate cells with the different queue names in the formula and then I can avg the 3 separate cells, wishing to only end up with the one formula cell per month.

I'm not sure how to OR/AND the 3 "Queue1" "Queue2" "Queue3" into * ("Queue1"=$B:$B) correctly and average then them without having to repeat a index lookup 3 times in a single Average formula.

Any pointers are appreciated in keeping the formula short and concise.

Thank you
 
Attached is a sample file, I trying to remove the need for columns Q:S, so that O contains the single formula, trying to avoid this:-

=IFERROR(AVERAGE(IFNA(INDEX($K:$K, MATCH(1, ($N2=$A:$A) * ("Queue_16"=$B:$B), 0)),"0"),IFNA(INDEX($K:$K, MATCH(1, ($N2=$A:$A) * ("Queue_17"=$B:$B), 0)),"0"),IFNA(INDEX($K:$K, MATCH(1, ($N2=$A:$A) * ("Queue_11"=$B:$B), 0)),"0")),"0")

Although it isn't really that bad however i'm sure there must be an easier formula to use and this is how I learn special things in Excel by trying to shorten my long formulas :smile:

Thanks
Jon
 

Attachments

  • Phone queue export temp.xlsx
    21 KB · Views: 8
Questionable Average

This formula starting in O2 will give the correct "mathematical" average and negate the need for the extra Columns : =IFERROR((IFERROR(INDEX(K:K,MATCH(N2&Q$1,A:A&B:B,0),1),0)+IFERROR(INDEX(K:K,MATCH(N2&R$1,A:A&B:B,0),1),0)+INDEX(K:K,MATCH(N2&S$1,A:A&B:B,0),1))/(3),""). But for March it gives a different average than in your example. An average is a Count divided by a Sum, of course. So I can only assume that you are summing only the cells that are greater than zero. Is that correct?
 
Be aware that IFERROR hides ALL errors, which may not be what you want ( a mistyped number could lead to an unwanted result). Only apply it when you are sure your formulas work
 
Updated Formula

This new formula will account for Queues that are absent a value as in your example. I had used the graphic in my previous formula.

(IFERROR(INDEX(K:K,MATCH(N2&Q$1,A:A&B:B,0),1),0)+IFERROR(INDEX(K:K,MATCH(N2&R$1,A:A&B:B,0),1),0)+INDEX(K:K,MATCH(N2&S$1,A:A&B:B,0),1))/((IFERROR(IF(MATCH(N2&Q$1,A:A&B:B,0)>0,1,0),0)+IFERROR(IF(MATCH(N2&R$1,A:A&B:B,0)>0,1,0),0)+IFERROR(IF(MATCH(N2&S$1,A:A&B:B,0)>0,1,0),0)))
 
Final Formula Adjustment

=(iferror(index(k:k,match(n2&q$1,a:a&b:b,0),1),0)+iferror(index(k:k,match(n2&r$1,a:a&b:b,0),1),0)+iferror(index(k:k,match(n2&s$1,a:a&b:b,0),1),0))/((iferror(if(match(n2&q$1,a:a&b:b,0)>0,1,0),0)+iferror(if(match(n2&r$1,a:a&b:b,0)>0,1,0),0)+iferror(if(match(n2&s$1,a:a&b:b,0)>0,1,0),0)))
 
Correcting inverse statement: "An average is a *Sum* divided by a *Count* , of course".
 
Thanks for the help, I have redesigned the spreadsheet so rather than having a single sheet i'm using a sheet per month and a dashboard sheet to display the information I need, I've learnt to use "" instead of zero for any errors, my minds eye prefers to see a zero in a cell rather than an empty cell, but it is easier to use a empty cell than add a countif etc for getting the average calculation.
 
I've learnt to use "" instead of zero for any errors, my minds eye prefers to see a zero in a cell rather than an empty cell, but it is easier to use a empty cell than add a countif etc for getting the average calculation.

The use of "" instead of 0 might cause problems if you need to use the result in another formula..
Be aware that "0" ( as shown in your examples) is a text string.. 0 is a number
 
Back
Top