Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    multiple variables in one formula

    How do I do this:
    Tried " If(And(Or " but cannot get it to work properly.

    If B1 ="all" and C1="Grp then D1 = "6"

    If B1 ="all" and C1="Grp-k, or "prv" then D1 = "7"

    If B1 ="am" or "pm" and C1="Grp, then D1 = "3"

    If B1 ="am" or "pm" and C1="Grp-k or "prv", then D1 = "3.5"

    Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Try this

    =IF(AND(B1="all",C1="grp"),6,IF(AND(B1="all",OR(C1 ="Grp-k",C1="prv")),7,IF(AND(C1="grp",OR(B1="AM",B1="PM" )),3,IF(AND(OR(C1="grp-k",C1="Prv"),OR(B1="AM",B1="PM")),3.5,0))))

    Good Luck

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    SkiPro,

    Another option would be a User Defined Function (UDF).
    Code:
    Option Explicit
    
    Function sComputeD(lRow As Long) As Single
    
    ' Calling formula: =sComputeD(Row())
    
       Select Case UCase(Cells(lRow, 2))
             Case "ALL"
                 Select Case UCase(Cells(lRow, 3))
                       Case "GRP-K", "PRV"
                           sComputeD = 7
                       Case "GRP"
                           sComputeD = 6
                       Case Else
                           'Action not specified!
                 End Select
             Case "AM", "PM"
                  Select Case UCase(Cells(lRow, 3))
                       Case "GRP-K", "PRV"
                           sComputeD = 3.5
                       Case "GRP"
                           sComputeD = 3
                       Case Else
                           'Action not specified!
                  End Select
            Case Else
                 sComputeD = 0
       End Select
       
    End Function     'sComputeD
    Of course if the ratio of 1/2 between all and (am or pm) holds true in all cases you can shorten the code to:
    Code:
    Option Explicit
    
    Function sComputeE(lRow As Long) As Single
    
    ' Calling formula: =sComputeE(Row())
    
            Select Case UCase(Cells(lRow, 3))
                  Case "GRP-K", "PRV"
                       sComputeE = 7
                  Case "GRP"
                       sComputeE = 6
                  Case Else
                      'Action not specified!
             End Select
             
             sComputeE = IIf(UCase(Cells(lRow, 2)) = "AM" Or _
                             UCase(Cells(lRow, 2)) = "PM", _
                             sComputeE / 2, sComputeE)
             
    End Function   'sComputeE
    Notes:
    1. The conversion of data to upper case to make the case of the entry non-error producing. Of course you will delete this feature if different case means different result desired.
    2. The name of the functions were arbitrary since you didn't specify what you were calculating. I would recommend changing them to something meaningful. Make sure you catch all instances (using search and replace choosing all occurrences is the best route here).
    SkiProUDF.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    An alternate bit of code (a take off of duthiet's code):

    Code:
    Public Function FindD(Value1 As Range, Value2 As Range)
    Application.Volatile
    Dim b As String, c As String
    b = Value1
    c = Value2
        If b = "all" And c = "Grp" Then FindD = 6: Exit Function
        If b = "all" And (WorksheetFunction.Or(c = "Grp-k", c = "prv")) Then FindD = 7: Exit Function
        If c = "Grp" And (WorksheetFunction.Or(b = "am", b = "pm")) Then FindD = 3: Exit Function
        If (WorksheetFunction.Or(c = "Grp-k", c = "prv")) And (WorksheetFunction.Or(b = "am", b = "pm")) Then FindD = 3.5 Else FindD = 0
    End Function
    Cell D2 =FindD(B2,C2) and copy down.

    To make it a bit more versatile, the two cells do not need to be adjacent and can be anywhere on the sheet.

    UDF1.png UDF2.png
    Last edited by Maudibe; 2014-01-09 at 07:08.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Thanks to RG who pointed out how to streamline this code futher.

    Code:
    Public Function FindD(b As Range, c As Range)
    Application.Volatile
        If b = "all" And c = "Grp" Then FindD = 6: Exit Function
        If b = "all" And (WorksheetFunction.Or(c = "Grp-k", c = "prv")) Then FindD = 7: Exit Function
        If c = "Grp" And (WorksheetFunction.Or(b = "am", b = "pm")) Then FindD = 3: Exit Function
        If (WorksheetFunction.Or(c = "Grp-k", c = "prv")) And (WorksheetFunction.Or(b = "am", b = "pm")) Then FindD = 3.5 Else FindD = 0
    End Function

  6. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks duthiet, RG, Maudibe

    Can the script be case [lower/upper] insensitive or must the case be specific as written in the script?

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    SkiPro,

    If you want to make the script case insensitive you need to use the UCase function as I did and test for Upper Case values. In this way it doesn't matter what case is used in the actual worksheet since in the test it gets converted to upper case. Note any of these methods can use this method although in the formula method it will add another level of complexity to an already complex formula. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Just an FYI. For the most part VBA is much more stringent than Excel is. Many excel formulas do not care about the case and are insensitive to it. Comparisons in VBA will be case-sensitive and the user must program (using UCASE or LCASE, typically) to make it insensitive if you want the UDF to be tolerant.

    Steve

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Case insensitive

    Code:
    Public Function FindD(value1 As Range, Value2 As Range)
    Application.Volatile
    b = UCase(value1)
    c = UCase(Value2)
        If b = "ALL" And c = "GRP" Then FindD = 6: Exit Function
        If b = "ALL" And (WorksheetFunction.Or(c = "GRP-K", c = "PRV")) Then FindD = 7: Exit Function
        If c = "GRP" And (WorksheetFunction.Or(b = "AM", b = "PM")) Then FindD = 3: Exit Function
        If (WorksheetFunction.Or(c = "GRP-K", c = "PRV")) And (WorksheetFunction.Or(b = "AM", b = "PM")) Then FindD = 3.5 Else FindD = 0
    End Function

  10. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maudibe, RG, Steve,
    Thank you for your help. Very helpful.

    Now!!, who can make it snow in Tahoe?

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    The following code will create a snow storm until an accumulation of 36 inches is reached:

    Code:
    Public Sub MakeItSnow()
    Dim snow As String
    Dim Tahoe As Range
    For Each flake In Tahoe
        snow = "Land on ground"
        Do Until snow = "3 feet"
            snow = snow + 1
        Loop
    Next flake
    End Sub
    Please, do not attempt to run this code (not tested)

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    That code look a bit flakey to me! ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That code look a bit flakey to me!
    "a bit flakey" is an understatement. Three feet is a LOT of Flakes...

    Steve

  14. #14
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Ok guys, back to work.

    I would like to create:
    If A="", then D= either "", or better yet I would think, GoTo End Function
    I added this to Maud's and it worked but cannot figure out how to call End Function which seems better and more eloquent.
    If a = "" Then FindD = "": Exit Function

    No idea how to do in RG's script.

    2) Within the script, can I add "hrs" after the number [6 hrs] and still have the number [6] work in calculations, such as Sum.
    I can do this in format, but I would like to know if it can be done in the scripts.

    3) I knew you would come through in my snow dilemma. After running Maud's proposed script, you are invited to my shoveling party. Bring shovels, I will bring the pizzas and beverage of your choice.

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    SkiPro,

    As written you can't add that to the function since it expects a number as a return value.
    Now if you're willing to return a 0 you can do this:
    Code:
    Function sComputeE(lRow As Long) As Single
    
    ' Calling formula: =sComputeE(Row())
    
            If Cells(lRow, 1) = "" Then
              sComputeE = 0
            Else
            
              Select Case UCase(Cells(lRow, 3))
                   Case "GRP-K", "PRV"
                         sComputeE = 7
                    Case "GRP"
                         sComputeE = 6
                    Case Else
                        'Action not specified!
               End Select
             
               sComputeE = IIf(UCase(Cells(lRow, 2)) = "AM" Or _
                             UCase(Cells(lRow, 2)) = "PM", _
                             sComputeE / 2, sComputeE)
             End If
                      
    End Function
    Of course you could turn off the show zeros in File->Options->Advanced.

    That said you could change it to return a Variant and then you could do what you want.
    Code:
    Function vComputeF(lRow As Long) As Variant
    
    ' Calling formula: =vComputeF(Row())
    
            If Cells(lRow, 1) = "" Then
              vComputeF = ""
            Else
            
              Select Case UCase(Cells(lRow, 3))
                    Case "GRP-K", "PRV"
                         vComputeF = 7
                    Case "GRP"
                         vComputeF = 6
                    Case Else
                        'Action not specified!
               End Select
             
               vComputeF = IIf(UCase(Cells(lRow, 2)) = "AM" Or _
                             UCase(Cells(lRow, 2)) = "PM", _
                             vComputeF / 2, vComputeF)
             End If
             
    End Function
    With this version you don't have to turn off the display of zero values.
    SkiProUDF.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •