| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused with If s and And s
I need some assistance with this formula below to make it easier to read and
functional. Everytime I start working on it something, in my head, tells me it is not right. I started recording a macro and then added all the "Ands" the formula because the recorder did not like them. What would be a way to write this? Range("B5").Select ActiveCell.FormulaR1C1 = "=IF(Left(RC[4],2)= ""50"", RC[1],_ If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)= ""H""),""Harcourt K-6"",_ If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)< ""H""),""Houghton K-6"",_ If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)= ""H""),""HRW"",_ If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)< ""H""),""McDougal"", "" "")))))" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confused with If s and And s
I changed the nesting of the IF's to eliminate the AND's. The 4th and 5th
rows in the formula below need to be placed on the same line and the same with 6th and 7th lines. Range("B5").Select ActiveCell.FormulaR1C1 = _ "=IF(LEFT(RC[4],2)= ""50"", RC[1]," & _ "IF(LEFT(RC[4],2)= ""30"",IF(MID(RC[4],7,1)= ""H"",""Harcourt K-6"",""Houghton K-6"")," & _ "IF(LEFT(RC[4],2)= ""40"",IF(MID(RC[4],7,1)=""H"",""HRW"", ""McDougal""),"" "")))" "Pablo" wrote: I need some assistance with this formula below to make it easier to read and functional. Everytime I start working on it something, in my head, tells me it is not right. I started recording a macro and then added all the "Ands" the formula because the recorder did not like them. What would be a way to write this? Range("B5").Select ActiveCell.FormulaR1C1 = "=IF(Left(RC[4],2)= ""50"", RC[1],_ If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)= ""H""),""Harcourt K-6"",_ If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)< ""H""),""Houghton K-6"",_ If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)= ""H""),""HRW"",_ If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)< ""H""),""McDougal"", "" "")))))" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confused with If s and And s
Perfect. Thanks.
"joel" wrote: I changed the nesting of the IF's to eliminate the AND's. The 4th and 5th rows in the formula below need to be placed on the same line and the same with 6th and 7th lines. Range("B5").Select ActiveCell.FormulaR1C1 = _ "=IF(LEFT(RC[4],2)= ""50"", RC[1]," & _ "IF(LEFT(RC[4],2)= ""30"",IF(MID(RC[4],7,1)= ""H"",""Harcourt K-6"",""Houghton K-6"")," & _ "IF(LEFT(RC[4],2)= ""40"",IF(MID(RC[4],7,1)=""H"",""HRW"", ""McDougal""),"" "")))" "Pablo" wrote: I need some assistance with this formula below to make it easier to read and functional. Everytime I start working on it something, in my head, tells me it is not right. I started recording a macro and then added all the "Ands" the formula because the recorder did not like them. What would be a way to write this? Range("B5").Select ActiveCell.FormulaR1C1 = "=IF(Left(RC[4],2)= ""50"", RC[1],_ If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)= ""H""),""Harcourt K-6"",_ If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)< ""H""),""Houghton K-6"",_ If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)= ""H""),""HRW"",_ If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)< ""H""),""McDougal"", "" "")))))" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| confused in the o.c. | New Users to Excel | |||
| Confused | Excel Worksheet Functions | |||
| Confused | Excel Worksheet Functions | |||
| Confused | New Users to Excel | |||
| I'm Confused........................................ | Excel Programming | |||
Linear Mode
