Consulting

Results 1 to 9 of 9

Thread: Formula assistance required.

  1. #1

    Formula assistance required.

    I'm hoping someone with a logical brain can help me.

    I have a spreadsheet that measures daily performance for different operations. I would like the overall performance for all the separate operations the preceding day to be the target for the operations the following day. I thought I'd come up with a formula but I've noticed it doesn't deliver what I require.

    I've attached a spreadsheet with a model of the information with a formula in column F that I thought would do the trick.

    The information I would want to see returned by a formula is displayed in column G.

    Any ideas folks would be appreciated.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    That makes no sense. 83 = 100, 91 = 90, and 81 = 80???
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    The information I would want to see returned by a formula is displayed in column G.

    Any ideas folks would be appreciated.
    The best idea I have would be for you to FULLY explain the magic behind column G

    Does 'Date' matter?

    Does 'Home' matter?

    Does 'Full' matter?

    What numbers are being percentaged, and against what other numbers?

    e.g. Col G= (Sum of 'Full' = Y and Date = Col A) / (Total of Full Count for that line to date)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Paul and Sam,

    Thanks for your replies and apologies as I was not clear.

    I have uploaded a revised spreadsheet that I hope is clearer.

    What I have is a spreadsheet that records if an item in col B is full or not, these items occur throughout the day with the earliest listed first. The number daily items in Col B may change so will not always be 5 separate items there could be 3 or 7 for example.

    What I would like is the previous days final Rolling % result to be the target for the following days items in Col B.

    The problem I have encountered is that as I enter the results take Tue 2/Jun/20 for example as they occur throughout the day, the Rolling % is updated accordingly.

    What I think I may now need is an additional column with a Target % and a formula that checks the date of the lines above until it encounters an earlier date and takes that occurrence's Rolling % as the target.
    Attached Files Attached Files

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    A VBA user defined function in column G would do it, but not sure how to make a worksheet formula do it


    Option Explicit
    
    
    Function Target() As Variant
        Target = Application.Caller.Offset(0, 1).End(xlUp).Value
    End Function
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    This array formula also does the same

    =INDEX($H$1:$H7,MAX(IF($H$2:$H7="",0,ROW($H$2:$H7))))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Thanks Paul and Bob and apologies for the late response I have a very poorly mother at the moment.
    You have given me a couple of ideas to work and and for that I am very grateful.


    Thanks once again.


    Barry

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No need to apologise Barry. Best wishes for your mother's recovery.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In cell G8:
    =SUMPRODUCT(($A$2:$A$31<$A8)*($C$2:$C$31="Y"))/COUNTIF($A$2:$A$31,"<" & $A8)
    Depending on your version of Excel, you may have to commit this formula to the sheet using Ctrl+Shift+Enter rather than the more usual Enter.
    Copy down.
    Note that the formula only refers to columns A and C, and doesn't need any helper columns.

    edit post posting: Oops, didn't notice Bob's response before I posted.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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