Index match issue

K

KDavidP1987

Board Regular
Joined
Mar 6, 2018
Messages
51
Greetings all,

I'm running into an issue with part of an INDEX MATCH array and don't know how to resolve it.

Context: Manager asked that I setup a table to calculate the average calls offered to agents on a 30 day rotation against the days they are WFH (work from home). So, I need needed to get the days they were WFH into a table, and the daily totals of calls they take. Then I needed to merge the data (done), and setup a calculation to provide the 30 day average for each day listed as WFH

Here is the code I came up with to calculate this. (it runs, but the numbers don't seem right)

Code: 
   =IF([@IsWFHDay]="WFH", AVERAGE(INDEX(Table1[[Agent]:[Calls Answered]],MATCH(1,([Agent]=[@Agent])*([Date]<=[@Date])*([Date]>=[@Date]-30),0),3)),"")

I've broken it down into pieces, and everything works correctly but one section.

Code: 
 MATCH(1,([Agent]=[@Agent])*[COLOR=#ff0000][B]([Date]<=[@Date])*([Date]>=[@Date]-30)[/B][/COLOR]

It seems the issue is with the Match section. I need it to pull an array of calls offered on days between the date the agent was WFH and going back 30 days. I originally tried using an AND operator in the match function, but that didn't work. How can I specify this range of time in the formula?

Anyone have an idea how I can accomplish this? Or if there is an easier way
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
If you're trying to calculate the average calls on WFH days...for all calls for the previous 30 days...
Try something like this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&[@Date]-30),"")

But...If you want the average for ONLY WHF days in that range...then this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[IsWFHDay],"WFH",[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&([@Date]-30)),"")


Is tht something you can work with?
 
Last edited:
Upvote 0
Ron Coderre said:
If you're trying to calculate the average calls on WFH days...for the previous 30 days...
Try something like this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&[@Date]-30),"")

Is that something you can work with?
Click to expand...

That is a great idea! However, when I tried it I receive #DIV/0! for some reason. Been trying to play around with it to return a good value. I don't have much experience using the AVERAGEIFS function, so I'm having trouble figuring out what it could be
 
Upvote 0
dateagentwhere?CALLS
01/03/2018agentaW105
01/03/2018agentbW120
01/03/2018agentcW95ASSUME WE WANT 5 DAY ANALYSIS
02/03/2018agentaH100
02/03/2018agentbW105agentaagentaagentbagentbagentcagentc
02/03/2018agentcH120HWHWHW
03/03/2018agentaH9501/03/201805/03/2018315210100440120600
03/03/2018agentbH10006/03/201810/03/20185200220305120420
03/03/2018agentcW105
04/03/2018agentaH120
04/03/2018agentbW95
04/03/2018agentcW100
05/03/2018agentaW105
05/03/2018agentbW120is this the sort of output you want ?
05/03/2018agentcW300
06/03/2018agentaH100
06/03/2018agentbW105
06/03/2018agentcH120
07/03/2018agentaH95
07/03/2018agentbH100
07/03/2018agentcW105
08/03/2018agentaH120
08/03/2018agentbW95
08/03/2018agentcW100
09/03/2018agentaH105
09/03/2018agentbH120
09/03/2018agentcW95
10/03/2018agentaH100
10/03/2018agentbW105
10/03/2018agentcW120
11/03/2018agentaW95
11/03/2018agentbW100
11/03/2018agentcW105
12/03/2018agentaH120
12/03/2018agentbW95
12/03/2018agentcH100

<colgroup><col><col span="6"><col span="2"><col span="7"></colgroup><tbody>
</tbody>
 
Upvote 0
I don't know why this isn't working, why it keeps returning #DIV/0!

Looking at the formula, there is no reason for it not to work, that I can find.
 
Last edited:
Upvote 0
How in the world did you paste a table? I tried typing one in earlier but couldn't figure it out.
(NVM, figured it out, I think [below])

Is there a way I could paste a picture from my computer, then you would see how it's laid out?

Its setup to do the calculations and then present the relevant info in a Pivot Table. Silly use of pivot table, I know... with essentially no actual calculations taking place (they're all on the worksheet itself). Just saves me time from having to refilter it each week and send the table to management.

AgentDateCalls OfferedCalls AnsweredIsWFHDayMonthHasWFH withinMonth30 Day WFH CO Avg 30 Day WFH CA Avg

<tbody>
</tbody>
Contoso Agent01/09/20183633OfficeJanuaryNo WFH
Contoso Agent01/10/2018 3028OfficeJanuaryNo WFH
Contoso Agent01/11/20182623OfficeJanuaryNo WFH
Contoso Agent01/12/20184241OfficeJanuaryNo WFH
Contoso Agent01/15/20184439OfficeJanuaryNo WFH
Contoso Agent01/16/20183635OfficeJanuaryNo WFH
Contoso Agent01/17/20183029OfficeJanuaryNo WFH
Contoso Agent01/18/201800OfficeJanuaryNo WFH
Contoso Agent01/19/20184440OfficeJanuaryNo WFH
Contoso Agent01/22/20184744OfficeJanuaryNo WFH
Contoso Agent01/23/20184040OfficeJanuaryNo WFH
Contoso Agent01/24/20183737OfficeJanuaryNo WFH
Contoso Agent01/25/20183736OfficeJanuaryNo WFH
Contoso Agent01/26/20184141OfficeJanuaryNo WFH
Contoso Agent01/29/20182017OfficeJanuaryNo WFH
Contoso Agent01/30/20182623OfficeJanuaryNo WFH
Contoso Agent01/31/20182624OfficeJanuaryNo WFH
Contoso Agent02/02/20183532OfficeFebruaryWFH
Contoso Agent02/05/20183331OfficeFebruaryWFH
Contoso Agent02/06/20184846OfficeFebruaryWFH
Contoso Agent02/12/20183332OfficeFebruaryWFH
Contoso Agent02/13/20182727OfficeFebruaryWFH
Contoso Agent02/14/20182825OfficeFebruaryWFH
Contoso Agent02/16/20183937OfficeFebruaryWFH
Contoso Agent02/19/20182524OfficeFebruaryWFH
Contoso Agent02/20/20183229OfficeFebruaryWFH
Contoso Agent02/21/20182827OfficeFebruaryWFH
Contoso Agent02/22/20184237OfficeFebruaryWFH
Contoso Agent02/23/20182725WFHFebruaryWFH38#DIV/0!

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
the 38 you see in the last record comes from

Code: 
 =IF([@IsWFHDay]="WFH", AVERAGE(INDEX(Table1[[Agent]:[Calls Answered]],MATCH(1,([Agent]=[@Agent])*([Date]<=[@Date])*([Date]>=[@Date]-30),0),3)),"")

The #DIV/!0! comes from

Code: 
 =IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&[@Date]-30),"")
 
Upvote 0
Ron Coderre said:
If you're trying to calculate the average calls on WFH days...for all calls for the previous 30 days...
Try something like this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&[@Date]-30),"")

But...If you want the average for ONLY WHF days in that range...then this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[IsWFHDay],"WFH",[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&([@Date]-30)),"")


Is tht something you can work with?
Click to expand...

Definitely the first one! However, haven't been able to get it to work. I'm using Office Excel 2013
 
Upvote 0
Ron Coderre said:
If you're trying to calculate the average calls on WFH days...for all calls for the previous 30 days...
Try something like this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&[@Date]-30),"")

But...If you want the average for ONLY WHF days in that range...then this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[IsWFHDay],"WFH",[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&([@Date]-30)),"")


Is tht something you can work with?
Click to expand...


After playing with it for awhile, I was able to break it down and figure out where the problem is in the code. It works (calculates the AVG) when I remove the date section of code. But when I include it I only get back #DIV/0!



Works:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered], [Agent],[@Agent]),"")

Doesn't work:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered], [Agent],[@Agent], [Date],"<="&[@Date], [Date],">="&[@Date]-30),"")

Code: 
, [Date],"<="&[@Date], [Date],">="&[@Date]-30

Is there a reason this code may not work? I did confirm the date is calculable! I did a calculation in another cell to subtract 30 from the date, and it returned the correct value (date)
 
Upvote 0
Ron Coderre said:
If you're trying to calculate the average calls on WFH days...for all calls for the previous 30 days...
Try something like this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&[@Date]-30),"")

But...If you want the average for ONLY WHF days in that range...then this:
Code: 
=IF([@IsWFHDay]="WFH", AVERAGEIFS([Calls Answered],[IsWFHDay],"WFH",[Agent],[@Agent],[Date],"<="&[@Date],[Date],">="&([@Date]-30)),"")


Is tht something you can work with?
Click to expand...

So sorry for all the trouble, and follow-up messages. I finally figured it out! The system I pull the data from provides the date as a string. It seems while individual formulas recognize this and calculate the value, for some reason the AVERAGEIFS function has trouble with it. I used the DATEVALUE() formula to translate the string to numerical date, and it works PERFECTLY now!

Thank you very very much, sir!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Populating multiple sheet names using Index, Match and Indirect
Replies
7
Views
176
Fluff
Fluff
E
  • Question
can't get INDEX-MATCH to copy entire column
Replies
2
Views
70
shift-del
shift-del
B
  • Question
How does one get around Xlookups duplicates vs filters size limitations?
Replies
5
Views
123
BEDE
B
sanantonio
  • Solved
Index > Match across broad (Column:Row) range
Replies
6
Views
271
KRice
K
F
  • Question
Tie-Breaking Question (Using Index/Match/Large)
Replies
3
Views
353
Fluff
Fluff

Forum statistics

Threads
1,215,212
Messages
6,123,655
Members
449,113
Latest member
Hochanz

Share this page

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