• Status: Solved
• Priority: Medium
• Security: Public
• Views: 326
• Last Modified:

# Excel: GROWTH with Inverted Column

I have the following column of numbers in F12:F18:

99.49
95.09
103.93
99.11
99.07
96.65
97.94

How do I construct a formula that effectively gives me =GROWTH(F18:F12)?

Best wishes,

John
0
jfdinneen
Asked:
• 5
• 2
1 Solution

Commented:
Simply select G12:G18, enter this formula:

=GROWTH(F12:F18)

and instead of hitting Enter, hit Ctrl+Shift+Enter (thus making it an array formula).

Excel will display it as:

{=GROWTH(F12:F18)}
0

Commented:
Formatting to two decimal places, the above returns:

Original      Growth
99.49       99.38
95.09       99.16
103.93       98.94
99.11       98.72
99.07       98.50
96.65       98.28
97.94       98.06
0

Author Commented:
Patrick

Thanks for the prompt reply.

Unfortunately, I do not want to create a new column, just a single result in G12. Forgive my ignorance, GROWTH(99.49, 95.09, 103.93, 99.11, 99.07, 96.65, 97.94) = 99.38, but GROWTH(97.94, 96.65, 99.07,, 99.11, 103.93, 95.09, 99.49) = 98.07 - I want the latter. Please advise?

Best wishes,

John
0

Commented:
I see now what you meant by "inverted".

As far as I know, you will either have to use a "helper" column, or use VBA to accomplish the inversion.  For example:

1) Put this formula in G12...

=INDEX(\$F\$12:\$F\$18,COUNT(\$F\$12:\$F\$18)-ROW(G1)+1)

2) Copy that through G18

3) In another cell, enter the formula...

=GROWTH(G12:G18)
0

Commented:
Using VBA, I created the function below, which is intended to return an array that is the "reverse" of the range fed in.  It only works properly if the range has:

1) 1 column and 2+ rows OR
2) 2+ columns and 1 row OR
3) a single cell

I then got the expected result with:

=GROWTH(ReverseRange(F12:F18))

``````Function ReverseRange(rng As Range) As Variant

Dim arr() As Variant
Dim Counter As Long

If rng.Rows.Count > 1 Then
ReDim arr(1 To rng.Rows.Count, 1 To 1) As Variant
For Counter = 1 To rng.Cells.Count
arr(rng.Cells.Count - Counter + 1, 1) = rng.Cells(Counter)
Next
ElseIf rng.Columns.Count > 1 Then
ReDim arr(1 To 1, 1 To rng.Columns.Count) As Variant
For Counter = 1 To rng.Cells.Count
arr(1, rng.Cells.Count - Counter + 1) = rng.Cells(Counter)
Next
Else
ReDim arr(1 To 1) As Variant
arr(1) = rng.Cells(1)
End If

ReverseRange = arr

End Function
``````
0

Commented:
I like this version much better.  Handles ranges with >1 row and >1 column, and is simpler.

=GROWTH(ReverseRange(F12:F18)) still works.
``````Function ReverseRange(rng As Range) As Variant

Dim arr() As Variant
Dim Counter1 As Long
Dim Counter2 As Long

ReDim arr(1 To rng.Rows.Count, 1 To rng.Columns.Count) As Variant

For Counter1 = 1 To rng.Rows.Count
For Counter2 = 1 To rng.Columns.Count
arr(rng.Rows.Count - Counter1 + 1, rng.Columns.Count - Counter2 + 1) = rng(Counter1, Counter2)
Next
Next

ReverseRange = arr

End Function
``````
0

Author Commented:
Patrick,

Excellent result. Thanks for the benefit of your expertise.

Best wishes,

John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 5
• 2
Tackle projects and never again get stuck behind a technical roadblock.