Multiple Conditions for a single cell

[robstark]

I'm trying to use an IF statement on the following:

IF(A1="Full",10)
IF(A1="Half",5)
IF(A1="Zero",0)
IF(A1="N/A","")

I want B1 to yield 10,5, 0 or blank based on what's entered into A1. I can't seem to put this altogether.

 

[FranzV]

You can use VLOOKUP to get a different value for each text.

Excel 2016 (Windows) 64 bit
	A	B	C
1	Full	10	IF(A1="Full",10)
2	Half	5	IF(A1="Half",5)
3	Zero	0	IF(A1="Zero",0)
4	N/A		IF(A1="N/A","")
5
6	Text	Result
7	Full	10
8	Half	5
9	Zero	0
10	N/A
Hoja1

Cell Formulas
Range	Formula
B1	=VLOOKUP($A1,$A$7:$B$10,2,0)
B2	=VLOOKUP($A2,$A$7:$B$10,2,0)
B3	=VLOOKUP($A3,$A$7:$B$10,2,0)
B4	=VLOOKUP($A4,$A$7:$B$10,2,0)
B10	=""

 

[jeffreybrown]

Try...

=LOOKUP(A1,{"Full","Half","N/A","Zero"},{10,5,"",0})

 

[robstark]

Franz- Thanks, but the second solution was more fitting.

Jeff- this did it!

Now I realize I have a different quandary...

I need to sum up my points in column B and get a perc. I do not want any "N/A" entries in column A to count for/against the total score. What would the formula be?

Example:
A1=FULL, B1=10
A2=HALF, B2= 5
A3= N/A, B3= (Blank)

B4 Sum= 15 (B1 + B2)

If I were to get a percentage I would want B5 to be:
15 (SUM of B!:B3)
20 (SUM of Full credit of A1 + A2. A3 would be a neutral cell)

 

[jeffreybrown]

I suppose there is more than one way to crack this nut, but what come to mind...

If Full is worth 10, but not all entries might be full and in this case, the Sum is 15, but if the possible total would be 20.

The percentage is 75%

C1 =SUM(B1:B3)/(COUNT(B1:B3)*10)

      -A-- B- -C-
  1   Full 10 75%
  2   Half 5     
  3   N/A

 

[robstark]

There is and this works- thanks again!

 

[jeffreybrown]

You are most welcome and thanks for the feedback. Glad you have a working solution.