Forum Discussion
If statement to calculate tiered goals multiplied by number of locations
I need help writing the formula for a multi tier program with goals multiplied by # of locations
Tier 1 = 150
Tier 2 = 250
Tier 3 = 400
Tier 4 = 700
X the number of locations (3 stores-Tier 1=450)
I want to only have to enter column 5 which is current number of units.
Need help to get the other column to auto populate based on column 5.
Column 2 = (TIER)
Column 4 = (GOAL) Once a tier is achieved, this should change to the next goal-times the number of locations
Column 5 = (CURRENT UNITS) this is the column I want to enter and should change 2 and 4
Column 6 = (NUMBER OF LOCATIONS)
2 Replies
Could you provide some examples of possible inputs and the expected results? Thanks in advance.
HansVogelaar I'll do my best to explain the desired outcome.
BOLD columns (#of stores, total items and item A,B,C) are the only field I want into to be inputted into.
Need a formula to auto populate the Level and Goal based on the TOTAL ITEMS x # OF STORES. (LEVEL 1 = 150 ITEMS/STORE) IF 3 STORES, LEVEL 1 = 450
Once 450 is attained, then the LEVEL AND GOAL should adjust to LEVEL 2, 750 and so forth
As the levels are achieved, the EARNING should adjust for ITEM A, B, C. TOTAL ITEMS will not be the sum of ITEMS A,B,C, this is why this is not an autosum calculation.
This is not my actual sheet, rather a sample
I have written the below formula on my actual sheet to get the GOAL to auto adjust, but it is not working past the 2nd IF statement. in this formula, e19=150 from chart below, f19=250..., column 28= # OF STORES.
=IF([@Column5]<E19*[@Column28],E19*[@Column28],IF([@Column5]>=E19*[@Column28],F19*[@Column28],IF([@Column5]>=F19*[@Column28],G19*[@Column28],IF([@Column5]>=G19*[@Column28],H19*[@Column28]))))
I think if you can help me understand what is wrong with this formula, i can apply that to the other formulas i need to write to get the LEVEL and EARNINGS to auto adjust.
# OF STORES LEVEL GOAL TOTAL ITEMS EARNING ITEM A ITEM B ITEM C 3 160 60 30 30
LEVEL 1 LEVEL 2 LEVEL 3 LEVEL 4 GOAL 150 250 400 700 ITEM A $3 $4 $5 $6 ITEM B $2 $3 $4 $5 ITEM C $1 $2 $3 $4
