SOLVED - Sales + New quota does not match original quota

Hi,

I have to keep track of daily sales vs daily quotas for a number of stores. I also have to provide store managers with daily updates to their store's goals based on sales up to that date.

Quotas data is its own separate worksheet.

Daily sales data is its own separate worksheet.

I have a third worksheet that pulls the quotas and sales data for each store and makes it "presentable" so I can send each store manager a nice screen grab every morning.

Some days a store will go over the sales quota and some days the store will fail to reach the daily sales quota. This creates a variance that accumulates as each day goes by.

What I'm trying to do is to take that accumulated variance and distribute it to the upcoming days (1 unit) per day so that the actual sales + the new quota matches the original quota total.

I'm adding a sample sheet just in case.

Not quite sure if this would be easier as a plain formula, or if VBA would be the way to go. So that's why I'm posting to the General forum.

Thanks in advance.

Seeeebs

QUOTAS.xlsx

Hi Glenn,

The file I uploaded was an example from a larger file that I keep with data for multiple stores. I should have uploaded the whole file from the start.

The formula you provided works like a charm given a couple of conditions:

1. Variance total is a positive number.
2. Variance total is less than total of numbers left in the month.

But if the Variance total is negative (store is ahead of pace), or the Variance total is larger than the number of days left in the month; the formula won't work.

I've attached a new file here that shows the different scenarios. Not sure if the formula can be adjusted for those conditions, or if a more complicated approach would be needed.

QUOTAS V2.xlsx

Cheers and thanks for your help.

Seeeeebs

Quote from GlennUK

Explain the logic of what the process should be when the Variance total is larger than the number of days left in the month.

Sure.

Add 1 to each day until end of the month. If any variance remains; add 1 more starting from today and going down the dates until remaining variance is added.

Example:

Variance: 25

Days left in the month: 15

DATE | QUOTA | NEW QUOTA

6/16 | X | X + 1 + 1

6/17 | X | X + 1 + 1

6/18 | X | X + 1 + 1

6/19 | X | X + 1 + 1

6/20 | X | X + 1 + 1

6/21 | X | X + 1 + 1

6/22 | X | X + 1 + 1

6/23 | X | X + 1 + 1

6/24 | X | X + 1 + 1

6/25 | X | X + 1 + 1

6/26 | X | X + 1

6/27 | X | X + 1

6/28 | X | X + 1

6/29 | X | X + 1

6/30 | X | X + 1

I already figured out the way to account for cases where variance is less than 0. It's this one scenario that I can't get to work with just a formula.

Thanks,

Seeeeebs

Quote from GlennUK

Use this formula in F2: =IF(B2<today,0,C2+(ROUNDDOWN((SUM(C$1:C1)-SUM(D$1:D1,F$1:F1))/COUNTA(B3:B$31),0)))

Works like a charm.

I asked this very same question in the Google Sheets community forums and although the response I got there "worked". It involved array formulas (five of them) and multiple "helper" columns.

And then you just drop a "one-liner" that works? True mastery of your craft is when you can solve the problem at hand via the simplest route.

Thank you very much Glenn.

Seeeeeebs