Multiple Conditions for a single cell

robstark

New Member
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.

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

FranzV

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

Excel 2016 (Windows) 64 bit
ABC
1Full10IF(A1="Full",10)
2Half5IF(A1="Half",5)
3Zero0IF(A1="Zero",0)
4N/A IF(A1="N/A","")
5
6TextResult
7Full10
8Half5
9Zero0
10N/A
Hoja1
Cell Formulas
RangeFormula
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

Well-known Member
Try...

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

robstark

New Member
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

Well-known Member
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)

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

robstark

New Member
There is and this works- thanks again!

jeffreybrown

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

Replies
3
Views
526
Replies
5
Views
202
Replies
3
Views
88
Replies
6
Views
232
Replies
9
Views
189

1,190,677
Messages
5,982,218
Members
439,769
Latest member
trungminh2802

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back