SUM i to n without Using Cell References

Scooterman23

New Member
Joined
May 9, 2015
Messages
7
In excel, SUM(number 1, [number 2], ...) is used to sum an array of cells, however, I'm trying to go back to the mathematical usage of summation:

n
Σ f(n) = f(1) + f(2) + ... + f(n-1) + f(n)
i = 1

I'm trying to use this to plug in values to a function such as PV(i%, n,, F) to sum present worth values with a constant i% and F value and a varying n without making an array of PV values.

I have a feeling that excel won't be able to do this but I'd like to try.

Thanks in advance!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,769
Hi

Not clear to me

You mean you want add the PV values using a sequence of n's, something like

=PV(3%, 1,, 100)+PV(3%, 2,, 100)+PV(3%, 3,, 100)+ ... + PV(3%, 10,, 100)

?

If not, please post a clear numerical example with the expected resutl.
 

Scooterman23

New Member
Joined
May 9, 2015
Messages
7
Hi

Not clear to me

You mean you want add the PV values using a sequence of n's, something like

=PV(3%, 1,, 100)+PV(3%, 2,, 100)+PV(3%, 3,, 100)+ ... + PV(3%, 10,, 100)

?

If not, please post a clear numerical example with the expected resutl.

That is almost exactly what I'm trying to do. I may try to do something like =PV(3%, 3,, 100)+PV(3%, 6,, 100)+PV(3%, 9,, 100)+ ... + PV(3%, 12,, 100), but that would be simply 3 times whatever solution is.

Thanks!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,769
You can use

=SUM(PV(3%, {3,6,9,12},, 100))

or, since you have an arithmetic progression with common difference of 3 also

=SUM(PV(3%, 3*{1,2,3,4},, 100))

which makes it easier to adapt if you have many terms. For ex. for 10 terms:

=SUMPRODUCT(PV(3%, 3*ROW(INDIRECT("1:10")),, 100))
 

Forum statistics

Threads
1,089,662
Messages
5,409,597
Members
403,271
Latest member
Rajeev Muraleedharan

This Week's Hot Topics

Top