• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • 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:
jfdinneen
  • 5
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
jfdinneenAuthor 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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

Open in new window

0
 
Patrick MatthewsCommented:
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

Open in new window

0
 
jfdinneenAuthor Commented:
Patrick,

Excellent result. Thanks for the benefit of your expertise.

Best wishes,

John
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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