QUESTION :
Value = 20 is a constant number
Percentage increase to “Value” in levels of 2%, 4%, 5% up to 20%
I would like to enter in a cell the LEVEL of the object and it return the VALUE + the percentage increase.
Entering 1 return 20
Entering 2 returns 20+2%
Entering 3 returns 20+4%
The returned level is used in another calculation in a another cell such as
20+4% X 25 Units
As it is now each time the percentage goes up I have to adjust the formula. I’l like to be able to just enter the level and have it automatically adjust to the required increase. A dropdown menu would be great.
ANSWER :
Somewhere in the workbook, you should create a two-column table that relates your numerical input to a % increase.
Once you’ve created the table, you can refer to it in your formulas by using the VLOOKUP function. For instance, where your value is in A1, the numerical input is in B1, and the table is in E2:F6, you would use the formula
=A1*(1+VLOOKUP(B1,$E$2:$F$6,2,FALSE))
The VLOOKUP here will look up the value of B1 in the first column of the table and return the associated increase value in the second column of the table.
As for implementing a drop-down menu for the numerical input, you can do that using Excel’s Data Validation feature (on the Data ribbon). Just choose to allow a List and use the first column of the lookup table as the data.