| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I simplify this function
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 -- Mike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
Function WhichBox(pIndex As Integer) As Integer
WhichBox = Application.Min(Int((pIndex + 3) / 4), 4) End Function Bob Umlas Excel MVP "Mike NG" wrote in message ... 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 -- Mike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
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" wrote in message ... 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 -- Mike |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
On Fri, 13 May 2005 at 14:58:28, Bob Umlas (Bob Umlas )
wrote: 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 -- Mike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
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)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Mike NG" wrote in message ... 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 -- Mike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
Function WhichBox(piIndex As Integer) As Integer
WhichBox = Fix((piIndex - 1) / 4) + 1 End Function Regards, Edwin Tam http://www.vonixx.com "Mike NG" wrote: 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 -- Mike |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
This won't work when pIndex gets larger than 16. It'll give 4 for all pIndex
16. Edwin Tam http://www.vonixx.com "Bob Umlas" wrote: Function WhichBox(pIndex As Integer) As Integer WhichBox = Application.Min(Int((pIndex + 3) / 4), 4) End Function Bob Umlas Excel MVP "Mike NG" wrote in message ... 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 -- Mike |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 -- Mike |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
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 "Mike NG" wrote in message ... 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 -- Mike |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
Re-read the example. I made that mistake.
-- HTH RP (remove nothere from the email address if mailing direct) "Edwin Tam" wrote in message ... Function WhichBox(piIndex As Integer) As Integer WhichBox = Fix((piIndex - 1) / 4) + 1 End Function Regards, Edwin Tam http://www.vonixx.com "Mike NG" wrote: 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 -- Mike |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Edwin Tam" wrote in message ... This won't work when pIndex gets larger than 16. It'll give 4 for all pIndex 16. Edwin Tam http://www.vonixx.com "Bob Umlas" wrote: Function WhichBox(pIndex As Integer) As Integer WhichBox = Application.Min(Int((pIndex + 3) / 4), 4) End Function Bob Umlas Excel MVP "Mike NG" wrote in message ... 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 -- Mike |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
Nice approach Arvi :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Arvi Laanemets" wrote in message ... 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 "Mike NG" wrote in message ... 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 -- Mike |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
In vba, this general form can sometimes be calculated faster. Not sure on a
worksheet though. =1+(A1=5)+(A1=9)+(A1=13) -- Dana DeLouis Win XP & Office 2003 "Mike NG" wrote in message ... 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 -- Mike |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
On Fri, 13 May 2005 at 20:39:59, Ken Wright (Ken Wright
) wrote: 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
On Fri, 13 May 2005 at 20:03:20, Bob Phillips (Bob Phillips
) wrote: 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 -- Mike |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I simplify this function
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) "Mike NG" wrote in message ... On Fri, 13 May 2005 at 20:03:20, Bob Phillips (Bob Phillips ) wrote: 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 -- Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help me simplify this function.... | Excel Worksheet Functions | |||
| simplify a vlookup function | Excel Worksheet Functions | |||
| simplify function | Excel Worksheet Functions | |||
| Simplify Vlookup function in Excel | Excel Worksheet Functions | |||
| Simplify CrossJoin function | Excel Programming | |||
Linear Mode
