Relative Computations?

kawkawati

New Member
Hi,

I need help on this and I hope that we can keep it to Excel not VBA.

1. I would like help in creating a formula that would check whether a particular ticket number appeared within the last 7 days. and

2. If the above condition is true, is the EMP # 1st to handle that particular ticket.

 Ticket EMP # Day Ticket Occurrences Did ticket occur within the past 7 days? If yes = 1 Is EMP # 1st to handle ticket? If yes = 1 26402426 100102 29-Jun-14 5 26335978 100152 30-Jun-14 1 26418928 100156 1-Jul-14 1 26440649 100149 2-Jul-14 1 26451302 100130 3-Jul-14 1 26461054 100064 4-Jul-14 1 26402426 100064 5-Jul-14 5 26478696 100130 6-Jul-14 1 26487400 100130 7-Jul-14 1 26402426 100064 8-Jul-14 5 26507912 100064 9-Jul-14 1 26517702 100064 10-Jul-14 1 26527528 100177 11-Jul-14 1 26537573 100176 12-Jul-14 1 26546797 100130 13-Jul-14 1 26556225 100130 14-Jul-14 1 26402426 100130 15-Jul-14 5 26529592 100177 16-Jul-14 2 26587024 100175 17-Jul-14 1 26529592 100177 18-Jul-14 2 26606819 100176 19-Jul-14 1 26616371 100097 20-Jul-14 1 26402426 100042 21-Jul-14 5 26597903 100175 22-Jul-14 1 26659694 100080 24-Jul-14 1 26646268 100097 23-Jul-14 1 26671025 100116 25-Jul-14 1 26146601 100097 26-Jul-14 1

<tbody>
</tbody>

it would have been easier if only Excel had a "RANKIFS" function. Haha!

Thank you for your help.

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Within the last 7 days of what?

Within the last 7 days of what?

Take for example Ticket number 26402426. It occurred at least 5 times in the entire spreadsheet with different dates. The 7 days should be relative to when the ticket last occurred. I hope I'm making sense - and thank you for looking into this.

Maybe:

Excel 2010
ABCDEF
1TicketEMP #DayTicket OccurrencesDid ticket occur within the past 7 days? If yes = 1Is EMP # 1st to handle ticket? If yes = 1
22640242610010229-Jun-14500
32633597810015230-Jun-14111
42641892810015601-Jul-14111
52644064910014902-Jul-14111
62645130210013003-Jul-14111
72646105410006404-Jul-14111
82640242610006405-Jul-14500
92647869610013006-Jul-14111
102648740010013007-Jul-14111
112640242610006408-Jul-14500
122650791210006409-Jul-14111
132651770210006410-Jul-14111
142652752810017711-Jul-14111
152653757310017612-Jul-14111
162654679710013013-Jul-14111
172655622510013014-Jul-14111
182640242610013015-Jul-14510
192652959210017716-Jul-14211
202658702410017517-Jul-14111
212652959210017718-Jul-14210
222660681910017619-Jul-14111
232661637110009720-Jul-14111
242640242610004221-Jul-14510
252659790310017522-Jul-14111
262665969410008024-Jul-14111
272664626810009723-Jul-14111
282667102510011625-Jul-14111
292614660110009726-Jul-14111
Sheet1
Cell Formulas
RangeFormula
E2{=(C2>=(MAX(IF(A\$2:A\$29=A2,C\$2:C\$29))-7))+0}
F2{=E2*(C2=(MIN(IF(A\$2:A\$29=A2,C\$2:C\$29))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Welcome to MrExcel, by the way.

Thank you Andrew but I think the results should be reversed. 1's should be 0's and the 0's should be 1's.

Why do you say that, given "Did ticket occur within the past 7 days? If yes = 1"?

My bad. It should have been "Did ticket occur more than once within the past 7 days? If yes = 1"?

Is it?

=(COUNTIFS(A\$2:A\$29,A2,C\$2:C\$29,">="&(MAX(IF(A\$2:A\$29=A2,C\$2:C\$29))-7))>1)+0

confirmed with Ctrl+Shift+Enter.

Wow! That did it! Thank you Andrew!

Threads
1,219,695
Messages
6,149,775
Members
450,913
Latest member
mdsuther

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

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