LinkBack URL
About LinkBacks
Hi All,
Need some URGENT help on a formula. Here’s the attached data sheet.
My requirements is as follows:
• I need to calculate the number of Sales Rep logged in simultaneously across different slots. The comparison is to be done across different days of Month. In other words, the count of reps across different time slots across the 4 Mondays have to be calculated.
What I’ve done is:
• Calculated Average Hours per rep in “Column E”
• Converted the time slot into a Converted to Decimal using =HOUR(B2)+MINUTE(B2)/60 in “Column F”
• Populate the last slot time in “Column G”
• Deducted the Decimal value attained in “Column H” labeled as an Integer
• Calculated the time difference in “Column I”
• “Colum J” is a assumed figure of 0.20-hours
The requirement now is to be able to get to a formula that can help me auto populate the data – just by entering the data for the first 4 columns (A -D).
By Hard coding the values from Column I, I’m unable to flow that value. It should flow in the order as highlighted:
• The first cell from “Column K” which is K3 should pick values from E2 & D2 (first slot), K4 (highlighted in blue) should pick values E3 & D3 (second slot) with the value of “I2” (highlighted in green) remaining constant till the last slot highlighted in “RED”,
• Similarly in “Column L” the first cell L4 should pick values from E2 & D2 (first slot), L5 (highlighted in Orange) should pick values E3 & D3 (second slot) with the value of “I3” remaining constant and so on so fort till the end
• Lastly, I want to be able to replicate this across other data irrespective of the number of slots. (In the attached example, first Thursday of August 2 has 14 slots, while there are 19 slots for the August 23).
Request is to be able to do this using a Excel FORMULA and not a MACRO.
I’m in a really need to execute this at the earliest. Help will be truly appreciated!!!
Best regards,
Jai
Register To Reply
Bookmarks