# Need formula for average units needed to achieve goal.

1. ## 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!

2. ## 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)

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

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. ## 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. ## 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".

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

#### 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