Solved: sum of i=1 to n, (a x b)

Gustavo_Eduardo · 02-17-2023 06:25 PM

Hello friends. I need to calculate the sum from i=1 to n, of (a x b), does anyone know how to do it?
I have a table with monthly values, and I need to calculate a forecast of what will happen at the end of the year. The idea is that from some existing values, a projection to the future is created.

Gustavo_Eduardo

Suppose the word Payment with the letter P

If P1 = P2 = P3, the sum of P1 + P2 + P3 = 3 P1, therefore for n periods, the sum of periods would be equal to nP1

But what happens when there is an average rate that applies to all costs?

P1 = P1 = P1 x T^0

P2 = P1 x T = P1 x T^1

P3 = P2 x T = P1 x T x T = P1 x T^2

Pn = P1 x T^(n-1)

∑ Pn = P Total

P Total = P1 x ∑ T^n = P1 x { [ 1-T ^ (n-1) ] / [1-T] }

In this way we avoid creating complex calculations and using the gsheet to calculate externally

This formula is the result of the summation of the infinite series raised above. Thanks anyway everyone.

View solution in original post

TeeSee1

@Gustavo_Eduardo wrote:

the sum from i=1 to n, of (a x b)

Not sure what this means.

TeeSee1

If this is what you want...

Then

Forecast Monthly:

IF(
 [Actual Figure]<>0,[Actual Figure],
 SUM(
  SELECT(
   forecast[Actual Figure],
   [Actual Figure]<>0
  )
 )
 /
 COUNT(FILTER("forecast",[Actual Figure]<>0))
)

Forecast Cumulative:

SUM(
 SELECT(
  FORECAST Current Year[Forecast Monthly], 
  NUMBER([month]) <= NUMBER([_THISROW].[month])
 )
)

To simplify the expressions I cheated a little by creating a slice to limit the calculation to YEAR=YEAR(TODAY()). 😋

Gustavo_Eduardo

finite succession

∑ (Pn-1 x VR)

VR = Variation Rate

P = Payment

n = Month

Actually what I need is to calculate the forecast. In Argentina we have serious inflationary problems and it is necessary to anticipate through a forecast.

For example, we know the first and the second payment and we can obtain the variation rate and what I need is to obtain a forecast of what will happen in the next payment periods (in this case months)

Payment 1 = 100

Payment 2 = 105

The variation rate has been 5%, thus, I should calculate how much money the total of the 12 payments will cost me, since each payment receives an increase of 5%, which is the average variation rate already calculated previously.

It would be something like this:

Payment 1 = 100 = 100

Pay 2 = Pay 1 x 1.05 = 105

Pay 3 = Pay 2 x 1.05 = 110.25

Pay 4 = Pay 3 x 1.05 = 115.75

Pay 5 = Pay 4 x 1.05 = 121.55

Pay 6 = Pay 5 x 1.05 = 127.6

Pay 7 = Pay 6 x 1.05 = 134

Pay 8 = Pay 7 x 1.05 = 140

Pay 9 = Pay 8 x 1.05 = 147.75

Pay 10 = Pay 9 x 1.05 = 155.13

Pay 11 = Pay 10 x 1.05 = 162.89

Pay 12 = Pay 11 x 1.05 = 171.03

Total = 1590.55

Had the 5% increase rule not been applied, the cost would have been simply 1200. The variance between total 1 and total 2 is 32.54%.

What you would basically need is to get a way to calculate that percentage 32.54% with a formula. I don't know if it's possible, I'm going to investigate. If I get a solution I will leave it exposed.

Then either add up the total or find a formula to calculate the total percentage of the first payment, which can be used to multiply by the first payment to get the total amount.

Marc_Dillon

You can do that with the FV() formula in GSheets. It's sort of opposite of the original intention of the formula, so you just have to set the amount argument as negative. So for your above example:

=FV( .05 , 12 , -100 )

Gustavo_Eduardo

see my solution and thanks for your answer

dbaum

Maybe you're asking about how to accomplish the equivalent of SUMPRODUCT? See one technique in this recent thread.

Rifad

Not sure if moving average for projection can be calculated using AppSheet. Appsheet need data in database to show in the app.

Gustavo_Eduardo

Thanks Marc. I'm going to set it up like this and say it's fixed. The only problem is that I work with the appsheet base in this case and I don't know if it is possible to enter this formula there, will it be possible to create it manually?

Gustavo_Eduardo