If statement to calculate tiered goals multiplied by number of locations

New Contributor

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

@CrissyZ 

Could you provide some examples of possible inputs and the expected results? Thanks in advance.

@Hans Vogelaar 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 STORESLEVELGOALTOTAL ITEMSEARNINGITEM AITEM BITEM C
3  160 6030

30

 

 LEVEL 1LEVEL 2LEVEL 3LEVEL 4
GOAL150250400700
ITEM A$3$4$5$6
ITEM B$2$3$4$5
ITEM C$1$2$3$4