Excel percentage help

pug_in_a_bed

Hello, a little bit of help for an excel thickie if you will...

I have a spreadsheet which looks like this


I want to produce totals that increase by 5% each year, can anyone help with a simple explaination and forumual? All I keep finding is how to work out the percentage changes!

Thanks in advance

fred7777

Not sure what you mean about the totals but 5% is one twentieth so if a cell had =B19+(B19/20) in it it would show a 5% increase on the value of B19

pug_in_a_bed

Sorry if not very clear - what I mean is I want say, year 4s total of 2111 to increase by 5% in year 5, so looking for a forumla that will do this, if that helps?

MX5huggy

Multiply each cell by 1.05 then format the cells to show the required number of decimal places (2 for pence)

fred7777

pug_in_a_bed wrote: »

Sorry if not very clear - what I mean is I want say, year 4s total of 2111 to increase by 5% in year 5, so looking for a formula that will do this, if that helps?

OK, then the easiest thing is to use the =B19+(B19/20) equation in B20, assuming for the sake of argument that B19 is year 4s total. The next cell would then be B21 which should be =B20+(B20/20) and so on across.
You only have to put the equation in once then you can highlight the cell and drag it across the other cells to reproduce the formula in the others.

esuhl

MX5huggy wrote: »

Multiply each cell by 1.05 then format the cells to show the required number of decimal places (2 for pence)

That's exactly how I'd do it

GunJack

Don't you mean multiply the previous cell in the row by 1.05 ??? so, in the above example, 2015's totals would be 2014's multiplied by 1.05. That would be correct

KathysBoy

GunJack wrote: »

Don't you mean multiply the previous cell in the row by 1.05 ??? so, in the above example, 2015's totals would be 2014's multiplied by 1.05. That would be correct

Whilst this is technically correct, I would create the formula slightly differently. I would multiply the cell by 1 plus the percentage value in another cell. This would then allow you to change the percentage value easily (I've wasted many hours having to change what I call hard coding i.e +B19+(B19/20) or +B19*1.05)

So for example , assuming the years starting 2010 are column A, B, C etc , in the very top line (or anywhere you want really) I'd put .05 (formatted to show 5%). So assuming Year 4 values are say D2, D3, D4 etc, in the first year 5 cell (E2) I'd put the following formula (+D2*(1+$E$1))

Then if you changed E1 to .1 it would change by 10%. I'd then copy this formula down the column, so each cell would change if I changed the percentage (the $ signs are important as they fix the cell reference, as when you copy a formula, the cell reference is changed ).

I would put the percentage value at the top of each year (so B1, C1 , D1, etc) but I would copy ( so cell C1 would say +B1, D1 would say +C1 etc) them so you only have to change the percentage in one cell, unless you want different ones, in which case I'd just overwrite it.

Hope this is clear

GunJack

^^^^^^ yes, this is the sort of method I would use to implement the solution (and do so on a daily basis in work ), however I was just pointing out the error in the logic of the previous post(s). Bearing in mind the OP's original question, I thought it may have been possibly a little over their head for that time of night