# Thread: multiple variables in one formula

1. ## 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. 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. 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

4. 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

5. 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. Thanks duthiet, RG, Maudibe

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

7. 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

8. 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. 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. Maudibe, RG, Steve,

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

11. 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. Maud,

That code look a bit flakey to me! ROTFLOL.gif

13. That code look a bit flakey to me!
"a bit flakey" is an understatement. Three feet is a LOT of Flakes...

Steve

14. 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. 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

Page 1 of 2 12 Last

#### Posting Permissions

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