Either or Formula

[dbconn]

Need help again. Is there a formula that will do an either or calculation.
Example: If B1 is A, B2 would be 100 but if B1 is B, B2 would be 89. There
could be as many as 13 possibilities for B1, each resulting in a different
result in B2. Also, if there is nothing entered in B1, B2 needs to be 0. I
currently am using the formula
=IF(B18="A+",100,0) in C18 and =IF(B18="A",100,0) in D18 and
=IF(B18="A-",93,0) in E18, etc, and then =SUM(C1818) in Q18 to show the
result.

Thanks much

 

[Eduardo]

Hi,
I assume you have a list of your 13 possibilities in sheet2 column A the
different letters and column B the numbers. In B2 enter

=IF(B1="","",SUMPRODUCT(--(B1=sheet2!A1:A14),sheet2!B1:B14))

if this helps please click yes thanks

 

[Bernard Liengme]

If you wish to use IF with multiple conditions, you need to 'nest' the
formulas
Here is an example going to three levels
=IF(B18="A+",100,IF(B18="A",100,IF(B18="A-",93,0)))
In Excel version before 2007, nesting can go to 7 levels; in XL2007+ you can
go to 64 levels but it is very easy to make mistake in such a long formula

Here is a better approach - I will let you adjust the number values:
=HLOOKUP(A18,{"A+","A","A-","B+","B";90,85,80,75,70},2,0)

Now I will add the rrquirement: balnk in B18 to give zero
=HLOOKUP(A18,{0,"A+","A","A-","B+","B";0,90,85,80,75,70},2,0)
Note we test not for "" but for zero

You data could be in cells
=HLOOKUP(A18,Sheet2!A1:F2,2,0)
or
=VLOOKUP(A18,Sheet2!G5:H10,2,0)

best wishes

 

[Eduardo]

another option use

=IF(B1="","",SUMPRODUCT(--(B1={"A","B","C"}),{100,89,70}))

just complete with the 13 possibilities and the numbers for each one

 

[dbconn]

Your first formula was great, but like you said it was easy to make a
mistake. By using "show calculation", though, it was easy to find and
correct.

Many thanks