VBA Function for Extrapolation

[bn10ej]

Hi all,

I'm a VBA beginner and I'm trying to create a function in VBA for extrapolating the first, second, third or whatever ordinal number smaller than the max
(for instance =TakeMinMax(“A1:A100”,3) would take the third number smaller than the max in the range A1:A100).

Any ideas on how to do this?

Thanks in advance.

[shg]

Ignoring the programming language, what sort of algorithm would you apply?

[bn10ej]

I wasn't aware that I'd need to use an algorithm for this... I have been given this exercise and as a VBA beginner I'm not certain that I fully understand what I need to do to create that function.

[shg]

An alorithm is just a sequence of steps to achieve a result. How would you do it on paper?

[bn10ej]

I'd find the maximum in a data set (for example Q1 Sales). Then write a function for extrapolating this data (except the maximum) to find Q2 Sales. I'm stuck on how to write this function.

[shg]

Forget what the numbers mean.

You have a list of 100 random numbers, perhaps including some duplicates, paper, and a pencil. How would you find the number you're looking for?

BTW, this has nothing to do with extrapolation.

[bn10ej]

I'd rewrite the numbers in a decreasing order. The maximum would be at the top. The number that's one row down from the maximum would be the first ordinal number that's smaller than maximum, the number that's two rows down from the max would be the second ordinal number that's smaller than max and so on. Is that the right way of doing it?

I initially thought that the function would be =TakeMinMax("A1:A100",<Max), but this seems too simple.

[shg]

What if, after sorting, the numbers are 100, 100, 99, 98, 98, 95, 94, ...

What's the answer and why? Does it change your algorithm?

[bn10ej]

I'd need to know if having duplicates is a problem for this task. If it doesn't matter, then no adjustment would be needed to the algorithm, except that the first ordinal number smaller than the max would be 99, and not 100, and so on.

Alternatively, I could come up with a way to add 0.5 to one of the duplicates (assuming there is only one duplicate). Although this would get complicated if there were more than one duplicate.

[shg]

If you need not consider duplicates, then

=LARGE(A1:A100, 3)
=SMALL(A1:A100, 3)

[bn10ej]

How can I create a function like that in VBA? I've been trying to do that, but no luck so far. This is what I've tried:

Function TakeMinMax(r As Range, i As Integer) As Integer
Set r = "A2:A21"

TakeMinMax = Large(r, i)

End Function

[shg]

Please Login or Register  to view this content.

[mehmetcik]

Please Login or Register  to view this content.

[bn10ej]

Thank you. When I run this code, a macro window comes up asking to create a macro. Not sure why. So I'm not able to run the code. What am I doing wrong?

[shg]

1. Copy the code from the post

2. In Excel, press Alt+F11 to open the Visual Basic Editor (VBE)

3. From the menu bar in the VBE window, do Insert > Module

4. Paste the code in the window that opens

5. Press Alt+Q to close the VBE and return to Excel

[bn10ej]

I ran both codes (from post 12 and 13) in a module window and this has created two formulas in Excel. However, when I use the formulas to show the ordinal number from the range in Excel, I get zero in both cases. This is the formula in Excel: =TakeMinMax(A2:A21,3). What am I doing wrong?

[shg]

Dunno. Post the workbook.

[bn10ej]

It works now. I didn't realise that the function name in Sheet 1 code has to be the same as in Module 1 code for it to work. Thank you very much for your help!