Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

multiplying result itself number

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Multiplying a result by itself a number of times

Multiplying a result by itself a number of times

ResolvedVersion 2007

Chris has attended:
Excel Intermediate course
Excel Advanced course

Multiplying a result by itself a number of times

Hi,
Having problems with getting the right formula. I'm doing a variance analysis at work, and the result of my year 1 variance is£300. In year 2, this variance will occur again but also increase with the power of inflation @ 3.5%, hence be £310.50. The same will happen in year 3, but obviously it becomes £310.50 * (1+3.5%) = £321.37. This gives my total 3 years variance of £931.87 (£300 + £310.50 + £321.37). My variances last for 10 years.

So what I need is a formula that will multiply the result on itself by 1+ the inflation, over a set number of years, with the 2 factors being:
- the original £300 (say in cell A2)
- the inflation 3.5% (say in cell B2)

I've done it on a spreadsheet by creating 10 columns, having Yr 2 being Yr * (1+3.5%) and dragging it over the 10 columns(years) which is the right answer, but I need a formula that will return that same total but in just 1 cell (spreadsheet & data constraints!!!!)
Thanks in advance

Edited on Fri 13 May 2011, 16:37

RE: Multiplying a result by itself a number of times

Hi Chris,

Suppose your inflation rate is in a named cell called INFL and your year 0 value is in a5. In b5, use the following:

=a5*(1+INFL)^10

^ means raise to the power. Brackets get calculated 1st, then power, then multiply and addition way down the list, so you do need the brackets.

I was starting at a base year and then going forward for 10, reading your example you may only be going forward 9 years.

Hope this helps - otherwise get back to me!

Clare

Fri 20 May 2011: Automatically marked as resolved.

Excel tip:

Filtering Data in an Excel 2010 Worksheet

When you have an Excel Worksheet with masses of data, it's not going to be easy to sift through it. So, in order to view sections of data, you can use the filter tool. Select the cells you want to filter (no need to select the column headers), then click the Home tab on the Ribbon, click Sort and Filter (you will find this in the Editing Section) then click Filter.

You will now see arrows in the top row of all the columns. If you click on an arrow, it will give you some filtering options so you can sort your data into ''Smallest to Largest'' or ''Oldest to Newest'' and so on..

To turn off the filtering, go back to the Home tab and click the Filter button again.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.