Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel 2010 Multiple years

Excel 2010 Multiple years

ResolvedVersion 2010

Tue 15 Apr 2014, 16:18 Delegate Omar said...

Omar has attended:
Excel Advanced course

Excel 2010 Multiple years

Hi,

I would like to produce a model which can be either 1 year, 2 year or 5 years etc. I would like to create an option for say if a deal was for 5 years it would populate the profit and loss over the years with an IF a function I think? I am not sure how to do it.

Thanks

Tue 15 Apr 2014, 17:06 Edited on Tue 15 Apr 2014, 17:07 Trainer Gary said...

RE: excel 2010 Multiple years

Hi Omar

Thanks for getting in touch. An IF function would definitely work. However can I suggest a VLOOKUP instead? It will allow the model to grow and be easier to maintain in the long run.

I've attached a very crude model to give you an idea of how it could work.

The VLOOKUP pulls information from the table in rows 1 - 4. The results are in cells D8 and E8.

I hope it makes sense. Please ask if you require any clarification.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

VLOOKUP Models.xlsx

Tue 22 Apr 2014: Automatically marked as resolved.

Training courses

98.7 % recommend us

More live stats...

Training information:

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

Excel 2010 Multiple years

Excel 2010 Multiple years

RE: excel 2010 Multiple years

Attached files...

Excel articles

Excel tip:

Checking formulas with multiple operators