Excel Functions – the faces of SUM and ‘What if’ Scenarios

Excel functions – SUM, SUMIF and SUMIF Indirect

Excel functions: Excel iconAs you will all be aware the SUM function is the cornerstone of any Excel workbook system.

It can take two forms within your spreadsheet:

  • =sum(range)  e.g. =sum(b2:b7)
  • =sum(individual cells)  e.g. =sum(b2,b7,b9)

There are also two enhanced sum functions which are: SUMIF and SUM with INDIRECT.

=SUMIF(b2:b9,”>30″)  only add up these cells IF a cell value is greater than 30.

=SUM(INDIRECT(“b1”):B4)  this neat nested function allows for the fixing of b1 so that if you insert a row above the current row 1 , that row will still be used in SUM!

Hope you gain SUM use out of these tips!

 

Excel functions – ‘what if’ scenarios

This little used feature is one that could be used in businesses but it can also be used at home too. This feature is used alongside a number of financial functions within Excel.

It allows the ‘what if’ scenarios to be built particularly around functions like:

  • FV (Financial Value)
  • PMT (Payment)
  • Rate (Interest rate)

You basically select the variables of the function and calculate the end result.

So in the case of FV you may wish to calculate the end figure on a savings plan using different periods of time, interest rates and amounts of money per period of time.

You can create a neat Summarised Scenario of various choices where the variables change.

So in business, Scenarios could be used for asset depreciation and in the home for mortgage or loan repayments.

This is covered more in our Master Class Excel Bronze training course.

If you want to know more about any of the functions above, take a look on our website here. We have 5 levels of Excel courses, so there will no doubt be at least one course for you! If you have a a query, feel free to send us an email on johnlege@jplcomputer.co.uk.

 

We hope you liked this post, why not take a look at our previous one on renaming worksheets too?