Case Statement in Excel

altin

New Member
Joined
Nov 26, 2009
Messages
3
I already have created the below VB functions in Excel which work fine, but now I'm trying to create a case statement which can call these functions from a third cell. i.e I'd like to enter "All, H Better" or "All L Better" and the correct function to be signed/called automatically. Any suggestions. Thanks.

Function AllHbetter(currentscore As Double, Goal As Double)
If currentscore >= Goal Then
AllHbetter = 4
End If
If currentscore < Goal Then
AllHbetter = 1
End If
If currentscore = nul Then
AllHbetter = 0
End If
End Function
Function AllLbetter(currentscore As Double, Goal As Double)
If currentscore <= Goal Then
AllLbetter = 4
End If
If currentscore > Goal Then
AllLbetter = 1
End If
If currentscore = nul Then
AllLbetter = 0
End If
End Function
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Code:
'Method1
Function AllHbetter(currentscore As Double, Goal As Double)
If currentscore >= Goal Then AllHbetter = 4
If currentscore < Goal Then AllHbetter = 1
If currentscore = nul Then AllHbetter = 0
End Function
Function AllLbetter(currentscore As Double, Goal As Double)
If currentscore <= Goal Then AllLbetter = 4
If currentscore > Goal Then AllLbetter = 1
If currentscore = nul Then AllLbetter = 0
End Function
 
'Method 2
Function AllHbetter(currentscore As Double, Goal As Double)
Select Case currentscore
    Case Is >= Goal
        AllHbetter = 4
    Case Is < Goal
        AllHbetter = 1
    Case Else
        AllHbetter = 0
End Select
End Function
Function AllLbetter(currentscore As Double, Goal As Double)
Select Case currentscore
    Case Is <= Goal
        AllLbetter = 4
    Case Is > Goal
        AllLbetter = 1
    Case Else
        AllLbetter = 0
End Select
End Function

The reason Method one is so short is because your IF statements are single operations, If you only have one operation as the result of an if you don't need to put it on a second line and therefore don't need the end it

Just check the Syntax of the case statement, I free typed so it could be a little wonky.
 

altin

New Member
Joined
Nov 26, 2009
Messages
3
Thank you for your respond. I'd like to make this case statement to return the AllHbetter = 1 when currentscore is = 0 and AllHbetter = 0 when the currentscore = nul

Is a way how to do this ? Thanks again.
 
Last edited:

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Thank you for your respond. I'd like to make this case statement to return the AllHbetter = 1 when currentscore is = 0 and AllHbetter = 0 when the currentscore = nul

Is a way how to do this ? Thanks again.

If there are only two possible outcomes (1 or 0) I don't understand why you want a case statement, use an if.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top