Can I simplify this function

[Mike NG]

Function WhichBox(piIndex As Integer) As Integer

If piIndex < 5 Then
WhichBox = 1
ElseIf piIndex < 9 Then
WhichBox = 2
ElseIf piIndex < 13 Then
WhichBox = 3
Else
WhichBox = 4
End If

End Function


I am going round in loops with / and mod - I am sure I can do it with
one line of code, but how do I do it please, i.e.

PiIndex Returns
1 to 4 1
5 to 8 2
9 to 12 3
13 to 16 4

 

[Bob Umlas]

Function WhichBox(pIndex As Integer) As Integer
WhichBox = Application.Min(Int((pIndex + 3) / 4), 4)
End Function

Bob Umlas
Excel MVP

 

[Bob Phillips]

Function WhichBox(piIndex As Integer) As Integer

Whichbox = (pIndex -1) \4 +1

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike NG]

Function WhichBox(pIndex As Integer) As Integer
WhichBox = Application.Min(Int((pIndex + 3) / 4), 4)
End Function

Bob Umlas
Excel MVP

Thanks - that was quick

 

[Ken Wright]

No need for a UDF when Excel will handle it quite easily with standard
functions

Assuming number in A1

=MIN(4,FLOOR((A1+3)/4,1))

 

[Guest]

Function WhichBox(piIndex As Integer) As Integer
WhichBox = Fix((piIndex - 1) / 4) + 1
End Function

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Guest]

This won't work when pIndex gets larger than 16. It'll give 4 for all pIndex

16.

Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Bob Phillips]

Missed the limit

Function WhichBox(piIndex As Integer) As Integer

WhichBox = Application.Min(4, (piIndex - 1) \ 4 + 1)

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

When you want to use this function as worksheetsfunction, then no need for
an UDF. With piIndex in cell A2:
=MATCH(A2,{0;5;9;13},1)

The same as an UDF

Public Function WhichBox(piIndex As Integer) As Integer
WhichBox = Application.WorksheetFunction.Match(piIndex, Array(0, 5, 9,
13), 1)
End Function


Arvi Laanemets

 

[Bob Phillips]

Re-read the example. I made that mistake.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Did you read the spec?

............
ElseIf piIndex < 13 Then
WhichBox = 3
Else
WhichBox = 4 <===========

Doesn't mean the spec wasn't wrong, but Bob's function meets the spec given.

 

[Ken Wright]

Nice approach Arvi

 

[Dana DeLouis]

In vba, this general form can sometimes be calculated faster. Not sure on a
worksheet though.
=1+(A1>=5)+(A1>=9)+(A1>=13)

 

[Mike NG]

Did you read the spec?

...........
ElseIf piIndex < 13 Then
WhichBox = 3
Else
WhichBox = 4 <===========

Doesn't mean the spec wasn't wrong, but Bob's function meets the spec given.

Yes for the time being piIndex won't be greater than 16, but I am trying
to write a sudoku solver for 4x4x4 puzzles rather than the traditional
3x3x3, so would be looking to generalise this for any possible 5x5x5's
that may come along....

 

[Mike NG]

Function WhichBox(piIndex As Integer) As Integer

Whichbox = (pIndex -1) \4 +1

End Function

I nearly had that the first time exact I had / instead of \

Cheers Bob

 

[Bob Phillips]

Mike,

\ is integer divide. a \ b is the equivalent of Int(a / b).

Following your subsequent post, you might want to add

Function WhichBox(piIndex As Integer, optional seed As Long = 4) As Integer

Whichbox = (piIndex -1) \seed +1

End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)