multiple variables in one formula

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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).
35982-SkiProUDF
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

35983-UDF1 35984-UDF2

Reply | Quote

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

Reply | Quote

Thanks duthiet, RG, Maudibe

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Maudibe, RG, Steve,
Thank you for your help. Very helpful.

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

Reply | Quote

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)

Reply | Quote

Maud,

That code look a bit flakey to me! 35623-ROTFLOL

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

That code look a bit flakey to me!

“a bit flakey” is an understatement. Three feet is a LOT of Flakes…

Steve

Reply | Quote

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.

Reply | Quote

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.
36004-SkiProUDF
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Skipro,

I’m there!

As for your code, I am not sure what you want to achieve.

If a = “” Then FindD = “”: Exit Function will not work because the code does not define the variable “a” (without the quotes). If you change the “a” to a “b” or a “c”, then the line of code will work:

If b = “” Then FindD = “”: Exit Function
If c = “” Then FindD = “”: Exit Function

Code:

Public Function FindD(value1 As Range, Value2 As Range)
Application.Volatile
b = UCase(value1)
c = UCase(Value2)
    If b = “” Then FindD = “”: Exit Function
    If c = “” Then FindD = “”: Exit Function
    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

You can use one or the other or keep both. Keeping both is the same as this line:

If b = “” or c = “” Then FindD = “”: Exit Function

Code:

Public Function FindD(value1 As Range, Value2 As Range)
Application.Volatile
b = UCase(value1)
c = UCase(Value2)
    If b = “” Or c = “” Then FindD = “”: Exit Function
    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

You can not use End instead of Exit Function because the code will end abruptly and FindD will not be returned ending up with a #value! error. The Statement End Sub is the last line of a macro and cannot be called. Since it is not a line label, you cannot use it with the Goto statement. If you prefer to use a Goto statement instead of Exit Function, you can direct the flow to a line label prior to the End Sub statement.

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: GoTo EndProgram
    If b = “ALL” And (WorksheetFunction.Or(c = “GRP-K”, c = “PRV”)) Then FindD = 7: GoTo EndProgram
    If c = “GRP” And (WorksheetFunction.Or(b = “AM”, b = “PM”)) Then FindD = 3: GoTo EndProgram
    If (WorksheetFunction.Or(c = “GRP-K”, c = “PRV”)) And (WorksheetFunction.Or(b = “AM”, b = “PM”)) Then FindD = 3.5 Else FindD = 0
EndProgram:
End Function

Reply | Quote

RG,
Thanks. Are you saying I cannot End function if A=”” ? That is, just stop the script without returning any value, including null? I only need a value here for calculations if A = a value.

I thought I could figure this out in Maud’s example but on further testing, I cannot control the return when A=””. Any help here appreciated. Again, if we can just stop the script and get no return, this seems optimal, if not, a null return or any return I can control would be fine.

Reply | Quote

SkiPro,

Ok, I think we need to clarify that when you say “A” do you mean a variable called A or are you referring to Column A?

If the latter that’s what my last post does in two different manners. Please note that a User Defined Function by definition returns a value. So I have given you two methods to accomplish this, assuming you were referring to Column A, take your pick. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Skipro,

if you change the 0 (zero) to a “” I think that will do what you want.

Code:

If (WorksheetFunction.Or(c = “GRP-K”, c = “PRV”)) And (WorksheetFunction.Or(b = “AM”, b = “PM”)) Then FindD = 3.5 Else FindD = 0

to

Code:

If (WorksheetFunction.Or(c = “GRP-K”, c = “PRV”)) And (WorksheetFunction.Or(b = “AM”, b = “PM”)) Then FindD = 3.5 Else FindD = “”

Reply | Quote

Thanks Maud, RG

These extra queries are both useful and me trying to expand my ability for future needs.

Reply | Quote