Forum Discussion
If statement to calculate tiered goals multiplied by number of locations
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 |