Trying To Put IFs into a Function

[SkippyPB]

Ihave one thing I do in a formula that I don't know or haven't figured
out how to make it work in a function.

The formula looks like this:

IF(AND(299<Week1!$H3,Week1!$H3<350),4,0))+
(IF(AND(349<Week1!$H3,Week1!$H3<400),6,0))+
(IF(AND(399<Week1!$H3,Week1!$H3<450),8,0))+
(IF(AND(449<Week1!$H3,Week1!$H3<500),10,0))+
(IF(Week1!$H3>499,12,0)

Simply put, it looks at the value in Worksheet named Week1 - cell H3
(could be H4, H5, H6 depending, also could be Week2 - 17) ) and if
that number falls within the listed values, it awards points.

Any help putting that into a function that will work with the Week and
Offset shown above would be greatly appreciated.

In other words I'd like to have a function that looks like this:

=Bonus (W, Q)

Where the Function Bonus would look something like:

Public Function Bonus(W, Q)
? the incorporated IFs pointing to the proper cells
End Function

Here W is a number that would form the name of the Worksheet to look
at ("Week" & W) and Q would be the offset Pointing to cells starting
at H2 to pick up the value to analyze.

Any help would be most appreciated.

Thanks.

Steve

 

[Rick Rothstein]

I think this UDF will do what you want...

Function Bonus(WeekNumber As Long, RowOffsetFromH2 As Long) As Double
Dim CellValue As Double
CellValue = Worksheets("Week" & WeekNumber).Range("H2"). _
Offset(RowOffsetFromH2).Value
Select Case CellValue
Case Is < 300
Bonus = 0
Case Is < 350
Bonus = 4
Case Is < 400
Bonus = 6
Case Is < 450
Bonus = 8
Case Is < 500
Bonus = 10
Case Else
Bonus = 12
End Select
End Function

Note that the RowOffsetFromH2 argument is numbered starting at 0 (for H2),
then going to 1 (for H3), 2 (for H4), etc.

 

[Per Jessen]

Hi

I reduced your formula to this:

=IF(AND(299<Week1!$H3,Week1!$H3<350),4,IF(Week1!$H3<400,6,IF(Week1!$H3<450,8,IF(Week1!$H3<500,10,12))))



Public Function Bonus(W As Long, Q As Long) As Long
Dim TargetVal As Double
Application.Volatile
TargetVal = Worksheets("Week" & W).Range("H3").Offset(Q, 0)

Select Case TargetVal
Case Is < 300
Bonus = 0
Case Is < 350
Bonus = 4
Case Is < 400
Bonus = 6
Case Is < 450
Bonus = 8
Case Is < 500
Bonus = 10
Case Else
Bonus = 12
End Select
End Function

Regards,
Per

 

[SkippyPB]

Thanks to both you and Rick. I took what you both came up with and
changed it slightly to better fit my needs and it works great.

Thanks again.

Hi

I reduced your formula to this:

=IF(AND(299<Week1!$H3,Week1!$H3<350),4,IF(Week1!$H3<400,6,IF(Week1!$H3<450,8,IF(Week1!$H3<500,10,12))))



Public Function Bonus(W As Long, Q As Long) As Long
Dim TargetVal As Double
Application.Volatile
TargetVal = Worksheets("Week" & W).Range("H3").Offset(Q, 0)

Select Case TargetVal
Case Is < 300
Bonus = 0
Case Is < 350
Bonus = 4
Case Is < 400
Bonus = 6
Case Is < 450
Bonus = 8
Case Is < 500
Bonus = 10
Case Else
Bonus = 12
End Select
End Function

Regards,
Per

////
(o o)
-oOO--(_)--OOo-

Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and never amounted to much.

The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve