If I have a cell (J1) which is used as a score rating system in which 1 to 6 would mean I need to type LOW in another cell (Q1) or 7 to 14 would mean typing MEDIUM, with 15 to 36 meaning I would need to type HIGH is there a formula I can add to the result cell (Q1) to automatically enter the correct word? I was wondering about IF and the AND statements, but can't see the wood for the trees IYSWIM. Thanks -- -- Robbo 1500GL 1988 Goldwing (Waiting Sale) BMW K100 RS 1984 "Fairly Quick" status. Silver level BotaFOF #19. E.O.S.M 2001/2002/2003/2004. B.O.S.M 2003, 2004 .FURSWB#1 KotL..YTC449 PM#7 ..
If you're not worried about trapping errors put the following in Q1 =IF(J1>14,"HIGH",IF(J1>6,"MEDIUM","LOW"))
With three different outputs, plus three different ways for the data to be invalid (too low, too high, not numeric), stringing IFs and implied THENs and ELSEs together can get messy [1]. This may help: - Go Tools.. Macro... Visual Basic Editor. - Go Insert... Module - In the new module, paste in the following: Function Robbo(NumberIn) As String On Error GoTo ErrorBlock Dim Answer As String If Not IsNumeric(NumberIn) Then Answer = "Error- Not Numeric" Robbo = Answer Exit Function ' NB - - - - - - - End If Select Case NumberIn Case Is < 1 Answer = "Error- too low" Case 1 To 6 Answer = "LOW" Case 7 To 14 Answer = "MEDIUM" Case 15 To 36 Answer = "HIGH" Case Else Answer = "Error- too high" End Select Robbo = Answer Exit Function ' NB - - - - ErrorBlock: MsgBox "Error " & Err.Number & " in function Robbo. " & Err.Description Stop End Function ' Robbo - In Q1, type in =Robbo(J1) HTH [1] Though I see Colin's done an admirably quick-and-effective formula that will handle valid data. -- <8P Wizard Suzuki GS550 "I like that. Nicely shite" - TOG Golf GTi 16v ANORAK#17b BOMB#19 BOTAFOT#138 BREast#5 COFF#24 COSOC#8 DFV#11 STG#1
With three different outputs, plus three different ways for the data to be invalid (too low, too high, not numeric), stringing IFs and implied THENs and ELSEs together can get messy [1]. This may help: - Go Tools.. Macro... Visual Basic Editor. - Go Insert... Module - In the new module, paste in the following: Function Robbo(NumberIn) As String On Error GoTo ErrorBlock Dim Answer As String If Not IsNumeric(NumberIn) Then Answer = "Error- Not Numeric" Robbo = Answer Exit Function ' NB - - - - - - - End If Select Case NumberIn Case Is < 1 Answer = "Error- too low" Case 1 To 6 Answer = "LOW" Case 7 To 14 Answer = "MEDIUM" Case 15 To 36 Answer = "HIGH" Case Else Answer = "Error- too high" End Select Robbo = Answer Exit Function ' NB - - - - ErrorBlock: MsgBox "Error " & Err.Number & " in function Robbo. " & Err.Description Stop End Function ' Robbo - In Q1, type in =Robbo(J1) HTH [1] Though I see Colin's done an admirably quick-and-effective formula that will handle valid data. -- <8P Wizard Suzuki GS550 "I like that. Nicely shite" - TOG Golf GTi 16v ANORAK#17b BOMB#19 BOTAFOT#138 BREast#5 COFF#24 COSOC#8 DFV#11 STG#1
