PDA

View Full Version : [SOLVED] How to use user defined function (bucket)



Beatrix
12-11-2013, 03:39 AM
Hi All ,

I have below code to group number of days into the buckets. If the number of days is between 11-30 then it should return 1 month, if it's between 31-90 then 3 months etc..

I couldn't work it out which goes to D2m and which goes to D2Ms:doh:

Would you mind to have a look at the udf and give me an example please?:(

Thanks very much:bow:



Function Bucket(d2m As Integer, D2Ms As Variant) As Variant


Dim Fnd_bckt As Boolean

Dim d2m_cnt As Integer

Fnd_bckt = False
d2m_cnt = 1

While Not Fnd_bckt And d2m_cnt <= 6
Fnd_bckt = (d2m > D2Ms(d2m_cnt, 1)) And (d2m <= D2Ms(d2m_cnt, 2))
d2m_cnt = d2m_cnt + 1
Wend
If Fnd_bckt Then
Bucket = D2Ms(d2m_cnt - 1, 3)
End If

End Function

Jan Karel Pieterse
12-11-2013, 05:29 AM
Why use a UDF, you can utilise VLOOKUP.

Suppose a table like this (starting in cell A1):
Days Months
0 1
30 2
90 3
180 6

And you have a number of days in cell F1, then this formula gives the number of months corresponding to the table.

=VLOOKUP(F1,$A$1:$B$10,True)

Kenneth Hobs
12-11-2013, 06:52 AM
Can you explain more? What are you needing done? I am not sure if you are working with dates or not. Try showing us some example data and expected result. Returning 1 or 3 months is not understood. We can return the integer 1 or 3. I don't see a use for D2Ms().

Taken literally, you just want to send an integer of 11 to 90 and return 1 or 3. Obviously, you need to handle the less than 11 and more than 90 cases or text entries or just let it error like any function.

If you are working with a date1 and a date2 and looking at the number of days difference then VBA's DateDiff() can be used to get the days difference or just substract the two dates to get the days difference.

Beatrix
12-12-2013, 04:57 AM
Thanks very much for your reply and for your time Mr Hobs. I really appreciate it.:bow:

I was trying to help someone else so there was a misunderstanding about the bucket list. Finally I worked it out how to use the udf bucket after she gave me the right bucket list. Next time I'll ask for the sample data at the beginning. I couldn't see a use for D2Ms too before she sent me her data but I now understand bucket range goes to D2Ms and number of days for due date goes to D2m. I attached the spreadsheet for an example.

Thanks very much again.:friends:



Can you explain more? What are you needing done? I am not sure if you are working with dates or not. Try showing us some example data and expected result. Returning 1 or 3 months is not understood. We can return the integer 1 or 3. I don't see a use for D2Ms().

Taken literally, you just want to send an integer of 11 to 90 and return 1 or 3. Obviously, you need to handle the less than 11 and more than 90 cases or text entries or just let it error like any function.

If you are working with a date1 and a date2 and looking at the number of days difference then VBA's DateDiff() can be used to get the days difference or just substract the two dates to get the days difference.

Beatrix
12-12-2013, 05:03 AM
Hi Jan ,

Thanks very much for your reply. I got this query from someone else and was asked to find out how udf works. That's why use a UDF. tHANKS again.:thumb



Why use a UDF, you can utilise VLOOKUP.

Suppose a table like this (starting in cell A1):
Days Months
0 1
30 2
90 3
180 6

And you have a number of days in cell F1, then this formula gives the number of months corresponding to the table.

=VLOOKUP(F1,$A$1:$B$10,True)