Excel -Assign value to field combinations 1

[Freefall69]

I am using Excel 2016 and trying to figure out how to assign values to field combinations with a formula. Looking at status of equipment (Operation and Admin)
Based on unique query there are 3 possible combinations:

 

[Andrzejek]

Sure you can do a long formula, something like: =If(AND(if(and(.... and on and on, but I would create my own formula and use that.

Alt-F11 (VBA Editor), add a standard Module and paste this code:

Option Explicit

Function [blue]Freefall69[/blue](ByRef str1 As String, ByRef str2 As String) As String
Dim strOut As String

If str1 = "up" And str2 = "up" Then
    strOut = "Active"
ElseIf (str1 = "down" And str2 = "up") Or (str1 = "up" And str2 = "down") Then
    strOut = "Available"
ElseIf str1 = "down" And str2 = "down" Then
    strOut = "Not Active"
Else
    strOut = "What is that?"
End If

Freefall69 = strOut

End Function

And if you have in your Excel "up" and "down" in cells A2 and B2, in cell C2 you can enter:
[tt]=[blue]Freefall69[/blue](A2,B2)[/tt]

---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,
Thanks! That works perfectly!!

 

[dhookom]

Freefall69,
If the answer works for you, consider clicking the Great post! link to let other know the question has been resolved.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Great!
This is the power of your own VBA function. Easy to see, easy to add or modify.
You could write it like this in cell C3:
[tt]
=IF(AND(A2 = "up", B2 = "up"), "Active", IF(AND(A2 = "up", B2 = "down"), "Available", IF(AND(A2 = "down", B2 = "down"), "Not Active", "What?")))
[/tt]
but that's not fun....


---- Andy

There is a great need for a sarcasm font.