Calculating Cumulative value in single cell

[Usman Ghayoor]

Hi,

a very small query, I want to get a formula to calculate value in Cell K7, with only using Cell E4 and E7 i.e., without using F7:J7

 

[chirayu]

made this sample vba code that you could modify to suit your needs

 

[Usman Ghayoor]

thanks. is it possible to do without VBA?

 

[ANKUSHRS1]

Try this
=SUM(E7,FV(E4,COLUMN(F5:J5)-COLUMN(E5),0,-E7))

Since this is CSE formula...press CTRL+SHIFT+ENTER


Like this if it is exactly what you want.

 

[Usman Ghayoor]

Thank

Try this
=SUM(E7,FV(E4,COLUMN(F5:J5)-COLUMN(E5),0,-E7))

Since this is CSE formula...press CTRL+SHIFT+ENTER


Like this if it is exactly what you want.

thanks. but I don't want to use any cells apart from E4 & E7 i.e., I want to build a formula which uses increment % and base rent to calculate sum of 5 years total outflow.

 

[ANKUSHRS1]

maybe this

=SUM(E7,FV(E4,{1,2,3,4,5},0,-E7))

 

[Usman Ghayoor]

maybe this

=SUM(E7,FV(E4,{1,2,3,4,5},0,-E7))

Thanks. this does solve the problem for now. but is it possible to automate {1,2,3,4,5} as well as in the future we might have different years (term of the agreement). May be I am too wishful but excel can do anything

 

[ANKUSHRS1]

Thanks. this does solve the problem for now. but is it possible to automate {1,2,3,4,5} as well as in the future we might have different years (term of the agreement). May be I am too wishful but excel can do anything

Then you have to apply previous formula where is use column formula to automate this number.

 

[Usman Ghayoor]

Many thanks. But in previous formula even it is not automatic, as you will have to change the range of columns every time you change no of years

 

[ANKUSHRS1]

try this...it it with input value cell.

you can change E3 cell as per your requirements

if you dont want to input cell for dynamic value...you can use below formula...

=SUM(E7,FV(E4,ROW(INDIRECT("1:5")),0,-E7))

just change upper limit (5 in this formula) and press CSE.

 

[bosco_yip]

Or, this CSE dynamic formula,

=SUM(E7,FV(E4,ROW(INDIRECT("1:"&MATCH("zz",5:5)-6)),0,-E7))

Regards
Bosco

 

[Usman Ghayoor]

Many thanks @ANKUSHRS1 and @bosco_yip :-D
both formula work

 

[Usman Ghayoor]

is there a possibility that we don't have to use CSE ?
as I have 2,000 sites and its a mess to manage CSE in case of addition/deletions !

 

[Usman Ghayoor]

another complication is, what I have to discount all the cash flows at 5% as well and need to calculate PV in another cell for all the cashflows

 

[Hui]

Use Sumproduct instead of Sum

 

[Usman Ghayoor]

Use Sumproduct instead of Sum

thaks Hui, but it returns a #value error

 

[bosco_yip]

is there a possibility that we don't have to use CSE ?
as I have 2,000 sites and its a mess to manage CSE in case of addition/deletions !

Non-CSE formula :

=SUM(E7,FV(E4,MMULT(ROW(INDIRECT("1:5")),1),0,-E7))

or,

=SUM(E7,FV(E4,MMULT(ROW(INDIRECT("1:"&MATCH("zz",5:5)-6)),1),0,-E7))

Regards
Bosco

 

[Usman Ghayoor]

any

another complication is, what I have to discount all the cash flows at 5% as well and need to calculate PV in another cell for all the cashflows

any help on this ?

 

[Peter Bartholomew]

To calculate the amount for each year you could use the named formula 'Amount'
=Base*(1+Incr)^Yr
If you choose you could output the values as a CSE array or, in future, as a dynamic (spilt) array.
In any case the sum would be given by
=SUM(Amount)
An alternative is to sum the geometric sequence algebraically giving the result
= Base * (1+Incr) * ( (1+Incr)^n - 1 ) / Incr
where n is the number of years.

 

[Usman Ghayoor]

Many thanks Peter. I have solved my query already with the help of Bosco given the fact that I didn't want to use CSE or extra cells.

Please find attached another file where I have a another query somehow related to the above but rather difficult. I want to calculate the answer in E2 with using only three cells E3, E4 & E7 and no CSE please.

I am not sure if this is possible but let see.

 

[Peter Bartholomew]

The results in the orange cells do not use the yearly incremented or discounted values; those ranges could be deleted if you so wish.
I agree that both the CSE control sequence and the rigidity of CSE arrays are pretty user-hostile. Despite that, I had schooled myself to use them all the time in order to exploit the more structured thought process that comes with array formula. I only break an array down into its constituent elements as a last resort.

Now with the Excel calculation engine changing behaviour so that multicell range references automatically give rise to array calculation, my life is about to get a lot easier, though some may find it confusing!

To see what I have done, go into name manager and look at the formulas for 'incremented.amount' and 'discounted.amount'. Named formulas always have evaluated properly as array and, of course, it is not necessary to coordinate three digits to get CSE!

Another way to see what is going on is to use Evaluate Formula and step in to see the details of a calculation.

 

[Usman Ghayoor]

Many thanks. I have gone through your calculation the only challenge is I only want to use three cells E3, E4 and E7 and rest should be dynamic formula that take cares of change in no of years. it is possible ?

 

[Peter Bartholomew]

For me, using Office365 insider, not only is it possible, it is beautiful in its simplicity.
If 'n' is the number of years you wish to model, the year numbers are given by
= SEQUENCE(1, n+1, 0)
[ SEQUENCE( rows, columns, start ) ]
and the amounts are
= initial.amount * (1+Incr)^yr
These arrays grow and diminish as you change the value of 'n'.

Unfortunately, traditional spreadsheet design is obsessed by the concept that the location on the worksheet is, in some mysterious way, relevant to the solution of a problem. Hence, the existence of functions ROW() and COLUMN() and even the ridiculous A1 cell notation. It is a good thing that real-life objects do not have to be referenced by location rather than by name, otherwise most people wouldn't make it through the day past breakfast!

 

[Peter Bartholomew]

@Usman Ghayoor
For you, more indirect approaches are necessary. For example, it is possible to define a range
= OFFSET( Y0, 0, 0, 1, n+1 )
and the year number array is then generated from the normalised column location
= COLUMN( OFFSET( Y0, 0, 0, 1, n+1 ) ) - COLUMN( Y0 )

Since you don't want to use array formulas (not CSE anyway) you could populate the range by using the formula
= COLUMN() - COLUMN(Y0)
which is filled right until you get bored. In keeping with normal spreadsheet philosophy, arrays are not something with a defined number of terms, they are simply a matter of the user determining how far to fill the formula range (it is all action-led).

The amounts and discounts can then either be defined in terms of range names,
= initial.amount * (1+Incr)^yr
= amount / (1+rate)^yr
in which case they will pick up individual values for the calculation by implicit intersection or you can give up all semblance of order and meaning and use relative referencing
= $E$2 * (1+$E$4)^X$7
= X$9 / (1+$E$4)^X$7
again, filling to the right until you reach the n-th year.

If you do not use the array calculations within named formulas the summation will require the definition of (old style) dynamic arrays, as you suggest. One way of creating these is to intersect the ranges you wish to sum with the columns that cover the years you want to take into consideration. This can be achieved by defining a range 'timeperiod'
= OFFSET( Y0, 0, 0, 10, n+1 )
and using formulas
=SUM(amount timeperiod)
=SUM(discounted timeperiod)
where the juxtaposition of ranges implies range intersection.

 

[Peter Bartholomew]

Please accept my apologies, I demonstrated the spreadsheet ideas on an Office 365 insider edition but did not test on a regular production version of Excel. Because @Usman Ghayoor had specified 'no CSE', I blocked the array calculation on 365 by using the implicit intersection operator "@" preceding the named arrays 'amount' and 'yr'. This appears to have been too severe a test of forward compatibility and generated 'unknown functions' as in
_xlfn.SINGLE(amount) and _xlfn.SINGLE(yr).

Removing the functions should return the workbook to a working state. Other strategies are to use CSE or to introduce additional names using relative referencing.