I have a column with surface area of land and want to calculate the rent in Excel. The rent is given as:
Surface m[sup]2[/sup] Money
0-300 210
301-600 420
601-1001 700
1001-2000 1050
2001-3000 1400
3001-4000 1750
4001 and more 1750 plus 350 for every 1000m[sup]2[/sup]
Just a note: You see how, in the example, a 5000m area is $1750 and a 5001m area is $2100? Technically, 4001+1000 is 5001 so that’s what I used in the formula. If that’s not what you want, just change the “4001” (after the “TRUNC”) to 4000.
You can use the VLOOKUP function to search the rent table - it looks in the leftmost column of a list(aka table) and returns that value or an adjacent column… whatever you want. I think it’s the best approach for your case. There are some good examples in Excel help. It can find an exact match, or in your case the value closest without exceeding.
You would have to fudge the straight VLOOKUP with an IF to handle the case for areas over 4000.