Repeat and sum formula results - single cell solution?

[ciltep]

Hi all,

Seems like this should be easy, but I'm scratching my head to figure out a solution. I've got the following formula:

(PxI^t) x E

In the formula, "P", "I", and "E" are all constants but would point to an entry cell for each value.

I'd like to create a single-cell formula that references to another single cell for the value of "t." Where I'm stuck is that I'd like the formula to calculate 1 through "t" times and then sum the results of each of those 1 through "t" calculations. For example, if I enter "10" for "t"...let's say in cell A1, then I'd like the formula to calculate 10 separate times (with "t" 1 through 10) and then sum the result in a single cell.

I can achieve the desired result by just listing my "t" values in a column and then copying the formula down in the next column and then summing, but it sure would be nice to have the calculation run all in one cell...especially when "t" starts to become a large number.

Would appreciate any help anyone could provide.

Thanks!

Phil

 

[MrKowz]

Welcome to the forums!


Try out this custom function. To use it, do the following:

1. Right click on your worksheet's tab and click "View Code" to open the VBA editor
2. Insert>Module
3. Copy/paste the following code into the module
4. In your worksheet, use the function =PIE(P,I,t,E) (where P, I, t, and E are your linked cells)

Public Function PIE(P As Double, i As Double, t As Double, E As Double)
Dim j As Long
For j = 1 To t
    PIE = PIE + ((P * i ^ j) * E)
Next j
End Function

 

[jasonb75]

Maybe

Excel Workbook
	A	B	C	D	E
1					(PxI^t) x E
2	P	I	T	E	Result
3	2	3	10	4	708576
Sheet

 

[ciltep]

Holy cow..it works! Thank you so much. I'm thrilled at the result, but feel a bit embarrased to say that I have no idea how you just did what you did. I've been a long time lurker on this board, but never have posted a question before. Thanks again for the lighting quick response and the perfect solution!

- Phil

 

[ciltep]

Thanks to you too jasonb75. I will try that solution as well.