Hi - Dave here.
Happy Friday!
One of the challenges with Excel is that it's complex. It's an old application with many layers of features, and it's not uncommon to get lost in the weeds when trying to find a solution to a problem. However, there are new functions in Excel that are making things easier.
A good example is the TAKE function, which makes it simple to retrieve the "last n entries" in a list or table. In the worksheet below, the "old" way to average the "last n rows" in the data shown is to use the OFFSET function like this:
=AVERAGE(OFFSET(C5,COUNT(data)-n,0,n))
With the TAKE function, the new formula looks like this:
=AVERAGE(TAKE(data,-n))
What's cool about TAKE is that you can use negative numbers to refer to rows at the
end of the list. I think you'll agree the second formula is easier :)
[
Download the workbook and read the full explanation]
The article above has a complete explanation. I've included formulas for both TAKE and OFFSET. Both formulas use a technique called "nesting", where an
inner function returns a result to an
outer function, which returns the final result. Nesting is the key to building more advanced formulas. Download the workbook and have a look.
Excel formulas
The only way to be good with Excel formulas is to
practice.
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a nice weekend!
Dave
