Creating specific formula in VBA

[Buffettology]

Hey guys,
Its been a long time since I created a formula in VBA. I was wondering how you would go about creating a formula that averages the growth rates of a sequence of whole numbers. I would like to simply highlight 10 years of sales data (40 quarters or 40 columns) and have the formula spit back the a specific quarters average (say 4^th quarter or 10 columns of data). So let me provide an example for color:
Quarter: 1Q, 2Q, 3Q, 4Q, 1Q, 2Q, 3Q, 4Q
Sales: 10, 20, 30, 40, 50, 60, 70, 80, etc..

I would like to take every 4Q sales number and divide by 3Q and subtract 1 for growth rate (e.g. 40/30-1, 80/70-1, etc.)

In other words I essentially want: =average(40/30-1, 80/70-1, etc.) by just selecting all numbers.

Is this even possible? If so, how would I go about doing it?

This one is probably a tough one Im sure, so thanks in advance!

 

[sijpie]

I have created the following two functions for you. The first function does what you want it to. Your formula can be simplified, because you need to subtract the 1 only once, not for every pair.
i have built in a lot of checks into the function to make it robust. see the comments. Please leave all the comments in place. They don't harm in any way and explain how the function works in case yo need to modify.
I have built the second help function to give a reminder on how to use the function, as you would need to build it in C+ or something in order to get the help displayed by Excel when entering.

Enjoy, let me know how you get on.



<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">' http://www.mrexcel.com/forum/excel-questions/912377-creating-specific-formula-visual-basic-applications.html</SPAN><br><br><br><SPAN style="color:#007F00">'------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Function</SPAN> AverageGrowth(rQuarters <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#007F00">' Function to get a growth factor for quarterly results. _<br>  factor is calculated as Average(Q4/Q3)-1.</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Use: worksheet function: _<br>    =AverageGrowth(B2:U2)  or _<br>    =AverageGrowth(C2:C20) _<br>  Each 3rd and 4th element of the range will be used._</SPAN><br><br><SPAN style="color:#007F00">' If less than 4 cells or more than one row or column is _<br>  entered the function returns #REF error. _<br>  If non numeric elements are encountered the function _<br>  returns #NUM error</SPAN><br>  <br><SPAN style="color:#007F00">'------------------------------------------------------</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, li <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lCm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lj <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> dG <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    vData = Range(rQuarters.Address).Value<br>    <SPAN style="color:#007F00">' check if one-dimensional range, more than 3 cells</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> rQuarters<br>        lRm = .Rows.Count: lCm = .Columns.Count<br>        <SPAN style="color:#00007F">If</SPAN> (lRm > 1 And lCm > 1) <SPAN style="color:#00007F">Or</SPAN> (lRm < 4 And lCm < 4) <SPAN style="color:#00007F">Then</SPAN><br>            AverageGrowth = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrRef)<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#007F00">' is the data in a Row or in a Column?</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> lRm > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' in column</SPAN><br>            <SPAN style="color:#007F00">' transfer range values into array for fast processing</SPAN><br>            vData = .Value<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#007F00">' transfer range values into array for fast processing, _<br>              transpose to get vertical array</SPAN><br>            vData = Application.WorksheetFunction.Transpose(rQuarters)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#007F00">' Now get the growth by comparing each 3 & 4th element</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> li = 4 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vData, 1) <SPAN style="color:#00007F">Step</SPAN> 4<br>        <SPAN style="color:#00007F">If</SPAN> (<SPAN style="color:#00007F">Not</SPAN> IsNumeric(vData(li, 1))) <SPAN style="color:#00007F">Or</SPAN> (<SPAN style="color:#00007F">Not</SPAN> IsNumeric(vData(li - 1, 1))) <SPAN style="color:#00007F">Then</SPAN><br>            AverageGrowth = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrNum)<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        Debug.Print vData(li, 1)<br>        lj = lj + 1 <SPAN style="color:#007F00">' counter for average</SPAN><br>        dG = vData(li, 1) / vData(li - 1, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> li<br>    <SPAN style="color:#007F00">' subtract 1 to get growth</SPAN><br>    AverageGrowth = dG - 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#007F00">'------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Function</SPAN> AverageGrowthHelp() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#007F00">'------------------------------------------------------</SPAN><br>MsgBox "Function to get a growth factor for quarterly results." & vbCrLf & _<br>  "Factor is calculated as Average(Q4/Q3)-1." & vbCrLf & vbCrLf & _<br>  "Use: worksheet function:" & vbCrLf & _<br>  "  =AverageGrowth(B2:U2)  or" & vbCrLf & _<br>  "  =AverageGrowth(C2:C20) " & vbCrLf & _<br>  "Each 3rd and 4th element of the range will be used."<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[Buffettology]

Wow great, thanks so much for the code!! But unfortunately I cant get it to work. The excel formula comes up but it doesn't do anything. Tried different was of selecting data. Any idea what could be going on here?

 

[Jonmo1]

Here's an array formula
=AVERAGE(IF(C1:I1="4Q",C2:I2/B2:H2-1))

It must be entered as an array with CTRL + SHIFT + ENTER
Notice the offset by 1 column in the ranges.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Quarter:	1Q	2Q	3Q	4Q	1Q	2Q	3Q	4Q
2	Sales:	10	20	30	40	50	60	70	80		Manually	0.238095
3
4											Array	0.238095
Sheet1

Cell Formulas
Range	Formula
L2	=AVERAGE(I2/H2-1,E2/D2-1)
L4	{=AVERAGE(IF(C1:I1="4Q",C2:I2/B2:H2-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Buffettology]

Ahh i c Mr. Excel. Very useful. Thank you very much.

 

[Jonmo1]

You're welcome.