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!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,737
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,737
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,078,393
Messages
5,339,936
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top