I apologize if this sounds confusing...I've been playing with formulas all day and can't seem to get this to work.
I have a sales incentive for my team where they need to get 25,000 points. To get these points...each dollar they sell is
worth a point, and every new collection is worth 3000 points. They need to have at least 10,000 points coming from sales.
I need a formula (or several formulas) to calculate how many points they still need to reach 25,000 based on how many
points they have in the categories (sales & collections). I need to show that if they have zero collections, they'll need
X# of sales, if they have 1 collection, they'll need X# of sales, etc.
If they enter in that they already have 4500 in sales points and 6000 in collection points (they sold 2 collections each
worth 3000 points), then to reach 25,000 they would need to either do:
Option A: 0 collections and $14,500 in sales
Option B: 1 collection and $11,500 in sales
Option C: 2 collections and $8,500 in sales
Option D: 3 collections and $5,500 in sales
Option D: 4 collections and $5,500 in sales
Option E: 5 collections and $5,500 in sales
Where I get stuck is the 10,000 sales minimum...and I also don't want
any negative numbers...so I haven't been able to figure out what to put as a formula if for example they have 30000 in
collection points, but 2000 in sales. Technically, they have 25000 points, but they still need 8000 in sales since 10000 has
to come from sales.
Can anyone help? Thanks so much...