Multiple Possibilitiy formula (IF or Table or Array)

D

Dennis

Using XL 2000 work
2003 home


Need easiest way to formula"ize" the following:
Column (A) is the linked-cell value in an XL
dropdown box.

Column (B) is the value that column (A) needs to be
converted to to upload to MS Access.

Description (A) (B)

Strongly Agree 2 4
Agree 3 3
Disagree 4 2
Strongly Disagree 5 1
No knowledge 6 0
(No Answer) 7 0

Therefore, the selection of "Strongly Agree" causes
the linked dropdown box to equal 2 in column (A).

I need the (B) value to equal 4 (per the above table).

In XL, what If statement (problem of limit to 7 no?)
or other formula in column B will efficiently do the value
conversion? Is there a better approach?

TIA Dennis
 
D

Dennis

Note a clarification:

I have the answer using an IF statement. For example,
=IF(C2=1,0,IF(C2=2,4,IF(C2=3,3,IF(C2=4,2,IF(C2=5,1, _
IF(C2=6,0,IF(C2=7,0)))))))

That said, my IF statement is maxed out at the nested
limit of seven.

(A) (B)

1 0
2 4
3 3
4 2
5 1
6 0
7 0

What formula or approach is better, if I have 25 data
items?

TIA Dennis
 
D

Dana DeLouis

If A1 only had the numbers 1-7, I would suggest the following for B1.

=MOD(2530,A1+4)

However, if A1 could have 25 different numbers, I would suggest the VLOOKUP
function.

HTH
Dana DeLouis
 
Top