+ Reply to Thread
Results 1 to 5 of 5

Help please - Need some URGENT help on a formula. Here’s the attached data sheet.

  1. #1
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Help please - Need some URGENT help on a formula. Here’s the attached data sheet.

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,779

    Re: Help please - Need some URGENT help on a formula. Here’s the attached data sheet.

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    ----------------------------------

    This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

    Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

    Have you started any work on this project yet?
    How far did you get?
    Where are you stuck?

    We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.
    Ben Van Johnson

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Help please - Need some URGENT help on a formula. Here’s the attached data sheet.

    And...Cross Posted too.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,169

    Re: Help please - Need some URGENT help on a formula. Here’s the attached data sheet.

    I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    HTH
    Regards, Jeff

  5. #5
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Help please - Need some URGENT help on a formula. Here’s the attached data sheet.

    Thanks Ben,

    I'm new so wasn't aware. How do I go about deleting this post. BTW, all I need is a formula to flow through as mentioned above. Rest all is done.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1