How to create an Excel formula w/different values?

[bk30350]

I want to create a form for qualifying sales prospects. For each criteria,
there will be up to 5 different variables for the data that will be entered.
Each one of the 5 variables will result in a different response.

Example:
Data entered: Desired resulting value on worksheet:
1 -5%
2 0
3 +5%
4 +10%
5 +15%

 

[firefytr]

Hi, maybe something like this ...

=CHOOSE(A1,-5%,0,5%,10%,15%)

For error handling ...

=IF(ISERR(CHOOSE(A1,-5%,0,5%,10%,15%)),-5%,CHOOSE(A1,-5%,0,5%,10%,15%))

For shorter/more efficient error handling with the morefunc.xll add-i
...

=IF(ISERR(SETV(CHOOSE(A1,-5%,0,5%,10%,15%))),-5%,GETV())


For another approach...

=--LOOKUP(A1,{1,"-5%";2,0;3,"5%";4,"10%";5,"15%"})

error handling ...

=IF(ISNA(--LOOKUP(A1,{1,"-5%";2,0;3,"5%";4,"10%";5,"15%"})),-5%,--LOOKUP(A1,{1,"-5%";2,0;3,"5%";4,"10%";5,"15%"}))

w/ morefunc.xll ...

=IF(ISNA(SETV(--LOOKUP(A1,{1,"-5%";2,0;3,"5%";4,"10%";5,"15%"}))),-5%,GETV())

NOTE: This is assuming anything below a 1 value in A1 will always giv
a -5% value. Format cell as percent (Ctrl + Shift + 5)

 

[Aladin Akyurek]

Looks like one of:

=INDEX({-0.05;0;0.05;0.1;0.15},A1)

=IF(A1,LOOKUP(A1,{1,-0.05;2,0;3,0.05;4,0.1;5,0.15}),"")

 

[Dana DeLouis]

If the entered data is really just integers 1-5, then another option might
be:

0.05 * number -0.1

HTH
Dana DeLouis