Question to the smart people here to help me with pivot tables.. I appreciate it.

[gorhamsj]

Hi... I am doing a problem for work and i dont know how to solve it.
it is a problem where we have days in one column and we have requests in the other... and using a pivot table or something I need to find out what are the three conseccutive ddays to find the highest request numbers...
so it could be day 1,2,3 or day 2,3,4 etc...

[TABLE="width: 703"]
<colgroup><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD]Comp[/TD]
[TD]Eyes[/TD]
[TD]Requests[/TD]
[TD]Zeros[/TD]
[TD]Avail Drivers[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2014[/TD]
[TD]Friday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2014[/TD]
[TD]Saterday[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]04/01/2014[/TD]
[TD]Sunday[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2014[/TD]
[TD]Monday[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!!!!

 

[oldbrewer]

[TABLE="width: 743"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD]Comp[/TD]
[TD]Eyes[/TD]
[TD]Requests[/TD]
[TD]Zeros[/TD]
[TD]Avail Drivers[/TD]
[TD]Hourly[/TD]
[TD][/TD]
[TD]helper[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2014[/TD]
[TD]Friday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2014[/TD]
[TD]Saterday[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/01/2014[/TD]
[TD]Sunday[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2014[/TD]
[TD]Monday[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]159[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]159 is biggest so sat sun mon 3,4,and 5 jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]there must be more to it !!![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 920"]
<colgroup><col><col span="7"><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD]Comp[/TD]
[TD]Eyes[/TD]
[TD]Requests[/TD]
[TD]Zeros[/TD]
[TD]Avail Drivers[/TD]
[TD]Hourly[/TD]
[TD][/TD]
[TD]helper[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2014[/TD]
[TD]Friday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2014[/TD]
[TD]Saterday[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/01/2014[/TD]
[TD]Sunday[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD="align: right"]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2014[/TD]
[TD]Monday[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]159[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2014[/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]147[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/01/2014[/TD]
[TD="colspan: 2"]Wednesday[/TD]
[TD][/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/01/2014[/TD]
[TD]Thursday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2014[/TD]
[TD]Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]189[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/01/2014[/TD]
[TD]Saterday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]156[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2014[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/01/2014[/TD]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2014[/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2014[/TD]
[TD="colspan: 2"]Wednesday[/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2014[/TD]
[TD]Thursday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/01/2014[/TD]
[TD]Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]search for biggest[/TD]
[TD="align: right"]189[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]use offset match to find[/TD]
[TD="align: right"]07/01/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]start date of 3 day run[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]formula is[/TD]
[TD="colspan: 4"]=OFFSET($J$3,MATCH($J$20,$J$4:$J$17,0)-2,-9)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gorhamsj]

Thanks a lot! I am trying to prepare for an interview A quick question. what is J3? and will this show you the start day of the 3 days which have the most accomululated requests? thanks... glassdoor said this:

2.1 8-10 questions are basic extraction from excel sheet but the trick is knowing some excel skills like pivot tables, e.g. what is highest completed trips in a given 24 hrs, what is the highest request in consecutive 72 hrs,

Thanks!!!!

 

[oldbrewer]

col J is the last column, J3 is the empty cell above 63 - starting from there run down the column to find $J$20 (the biggest 3 day total) and when you have found it go up 2 cells then go left 9 cells