• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Percentage increase table

Lara_1234

New Member
All

I have % increase against some months, i need the formula to match answer as shown .

QuestionMonth
Jan-23​
12%​
Feb-23​
8%​
Mar-23​
0%​
Apr-23​
10%​
Jan-23​
Feb-23​
Mar-23​
Apr-23​
May-23​
Jun-23​
Jul-23​
Aug-23​
Sep-23​
Oct-23​
Nov-23​
Dec-23​
Answer
12%​
20%​
20%​
30%​
30%​
30%​
30%​
30%​
30%​
30%​
30%​
30%​
 
Or, try this formula solution for all Excel 2010 versions and above.

In C4, formula copied across right:

=SUM(B4,IFERROR(LOOKUP(9^9,0+LEFT(INDEX($C1:$Z1,MATCH(C3,$C1:$Z1,0)+1),{1,2,3,4})),0))

81849
 

Attachments

  • Lookup+Sum.xlsx
    11.4 KB · Views: 4
Last edited:
Because I use 365, I decided to try a different approach.
Code:
= WRAPCOLS(input,2)
looked as if I were halfway there, but it turned out that the apparent percentages were text and not numeric :(
81880
A quick Lambda function to convert the text to percentages
Code:
Percentλ
= LAMBDA(x, IFERROR(TEXTBEFORE(x, "%") / 100, 0))
Addλ
= LAMBDA(x,y, x+y)
and another to add two numbers
gave the final form
Code:
= LET(
      table,  WRAPCOLS(input,2),
      dates,  TAKE(table,1),
      pcText, DROP(table,1),
      pc,     Percentλ(pcText),
      acc,    SCAN(0,pc,Addλ),
      VSTACK(dates, acc)
  )
giving
81881
Whether anyone would recognise this as a regular Excel solution is another matter!
 
Back
Top