Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Either or Formula
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(C18:P18) in Q18 to show the result. Thanks much -- dbconn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Either or Formula
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 "dbconn" wrote: 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(C18:P18) in Q18 to show the result. Thanks much -- dbconn |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Either or Formula
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "dbconn" wrote in message ... 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(C18:P18) in Q18 to show the result. Thanks much -- dbconn |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Either or Formula
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 "Eduardo" wrote: 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 "dbconn" wrote: 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(C18:P18) in Q18 to show the result. Thanks much -- dbconn |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Either or Formula
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 -- dbconn "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "dbconn" wrote in message ... 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(C18:P18) in Q18 to show the result. Thanks much -- dbconn . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|