multiplying result itself number
The UK's Number 1 for Microsoft Office Training Add this page to your favourites/bookmarksBookmark page
 
View printable version of pagePrintable version
Plus One Google
Customer: Sign in
Delegate: Sign in
Trainer: Log in

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

resolvedResolved · Urgent Priority · Version 2007

replyReply Fri 13 May 2011, 09:01Delegate Chris said...

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

For upcoming training course dates see: Pricing & availability

replyReply Fri 13 May 2011, 16:35 Edited on Fri 13 May 2011, 16:37Trainer Clare said...

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.

 

Please browse our web site to find out more about
microsoft+excel+training+course and other Microsoft training courses.

Excel tip:

Checking formulas with multiple operators

When dealing with formulas containing more than one operator (+, -, /, *), Excel follow standard BEDMAS order of operation rules. These rules specify the order that calculations will be performed in, regardless of how the formula reads left to right:

B = brackets
E = exponents
D = division
M = multiplication
A = addition
S = subtraction

It should be noted that multiplication and division are considered equal; as are addition and subtraction.

If you would like to check the order in which Excel is performing calculations in a formula, simply click on the cell containing the formula. Then go to Tools - Formula Auditing and select Evaluate Formula.

In the Evaluate Formula dialogue box that appears on your screen, click the Evaluate button to see how Excel calculates the formula result.

View all Excel hints and tips


Microsoft Certified Partner Accredited Training Provider: Institute of IT Training Institute of Leadership and Management - Certified Courses Security Seal verified by visa, mastercard securecard