How to calculate average rates earned on a lump sum?

 Imagine you invest $10,000 this year in a fund that promises the following returns over the next 5 years:

Your money would grow thusly.

It would be wrong to simple average the rates per year since this ignores any compounding. We use the function "RATE" from excel.

Wrong approach: (10 + 8 + 6 + 4 +2) / 5 =   6%

Correct approach: =RATE(5 , 0 , -10000 , 13358.44) = 5.962%

assigning a value based on price

 Dear students,  

I was asked for help with the grading tab, specifically on how to weigh each exam based on the performance. The example below should help. Is about allocating money according to price. 

Using vlookup in a range

 Imagine you are looking for the perfect sparkling wine. You have a database that looks like this.

The set has thousands of bottles to chose from. You want to begin the process by eliminating those deemed too cheap or too expensive. Your price ranges are as follow.

 
Your function should tell excel: Go look at the price that is stored in cell "G2", get it and find it in "Table array". Once you find it, bring the value stored in column 2 of "Table array". 

One problem is that vlookup can not understand "$15 - up". vlookup can look for only 1 value, so the first thing you want to do is to change those price ranges to a single price that vlookup could read.  Like the one below.

In red you see what does not work, in green what excel understands. It is key that the table array is sorted from low to high!

Excel will look place every price between $0 and $7 as "Cheap", anything between $7 and $15 as value... and so on. So in practice you create ranges in a vertical way.

The formula would look like this.