+ Reply to Thread
Results 1 to 5 of 5

Need formula for average units needed to achieve goal.

  1. #1
    Registered User
    Join Date
    07-19-2014
    Location
    San Diego
    MS-Off Ver
    MAC 2011
    Posts
    3

    Need formula for average units needed to achieve goal.

    Hello All,

    I am new here. This forum is AMAZING!

    I am having issues with a particular formula. I have a daily sales tracking sheet for my staff and am looking for a way to calculate the amount of units per day I need in the current week to obtain the goal.

    For example, if (hypathetically) someone worked 7 days and the goal was 7 (one per day) and we are about to start day 7 but only have 5 units, then the formula should reflect 2 units needed per day in this category to achieve the goal (2 units on the last day to make 7 total).

    I've attached what I have so far. In this I have put in some random numbers just to test it out.

    BP8 is the example goal.
    BP9 should reflect "6" since there is one day that hasn't been filled in for this category and that is what is needed to achieve BP8 (ignore the formula that is currently in there).

    How do I make this happen? As you can see, I will be copying and pasting this in several places, but I am lost. I am new to Excel, but am IN LOVE with it haha.

    Thanks for all of your help that you've put into this forum!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula for average units needed to achieve goal.

    Hi and welcome to the forum

    Try this for BP8 instead of what you have there...
    =SUMIF($E$7:$AZ$8,BP$7,$E8:$AZ8)

    Then for your answer, use this...
    =BN8-SUMIF($E$7:$AZ$8,BP$7,$E9:$AZ9)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-19-2014
    Location
    San Diego
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: Need formula for average units needed to achieve goal.

    Thanks for the response!

    As if this wasn't confusing enough, I told you the wrong cell, sorry!! BP8 is the goal (14 units), BI9 is actually where I'd like the units needed per day to reflect (6 in my example scenario). This is for "Up" category under my "Need Per Day" section.

    I know this takes a lot of effort and brain energy, so again, sorry and thanks for your help!!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula for average units needed to achieve goal.

    OK then put that 2nd formula in BI9 instead (its teh same thing, just in a different location)

    =BP8-SUMIF($E$7:$AZ$8,BP$7,$E9:$AZ9)

  5. #5
    Registered User
    Join Date
    07-19-2014
    Location
    San Diego
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: Need formula for average units needed to achieve goal.

    It's giving me a "#" sign in both places for some reason (I'm sure I'm doing something wrong). I copied and pasted the first formula in BP8 and the second into BI9 and they both have the same error: "Formula Refers To Empty Cells".

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula regarding daily goal calculation needed
    By LaserDance in forum Excel General
    Replies: 7
    Last Post: 01-20-2015, 07:46 PM
  2. Replies: 3
    Last Post: 06-12-2014, 09:29 AM
  3. Formula for number of perfect ordered needed to achieve certain target.
    By kokoli84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2014, 08:06 PM
  4. Excel 2007 : calculating numbers needed to achieve best result
    By candiecane_81 in forum Excel General
    Replies: 3
    Last Post: 06-20-2011, 12:40 AM
  5. Replies: 1
    Last Post: 09-07-2009, 06:50 AM

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