Relative Computations?

kawkawati

New Member
Joined
Sep 10, 2014
Messages
5
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 = 1Is EMP # 1st to handle ticket? If yes = 1
2640242610010229-Jun-145
2633597810015230-Jun-141
264189281001561-Jul-141
264406491001492-Jul-141
264513021001303-Jul-141
264610541000644-Jul-141
264024261000645-Jul-145
264786961001306-Jul-141
264874001001307-Jul-141
264024261000648-Jul-145
265079121000649-Jul-141
2651770210006410-Jul-141
2652752810017711-Jul-141
2653757310017612-Jul-141
2654679710013013-Jul-141
2655622510013014-Jul-141
2640242610013015-Jul-145
2652959210017716-Jul-142
2658702410017517-Jul-141
2652959210017718-Jul-142
2660681910017619-Jul-141
2661637110009720-Jul-141
2640242610004221-Jul-145
2659790310017522-Jul-141
2665969410008024-Jul-141
2664626810009723-Jul-141
2667102510011625-Jul-141
2614660110009726-Jul-141

<tbody>
</tbody>

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

Thank you for your help.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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