Excel question. How can I do this?

Factual Questions
1

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]
2

Use this (long) formula in the rent column:


=IF(A2<=300, 210, IF(A2<=600, 420, IF(A2<=1001, 700, IF(A2<=2000, 1050, IF(A2<=3000, 1400, IF(A2<=4000, 1750,1750+(TRUNC((A2-4001)/1000)*350)))))))

Here’s an example if you need one.

3

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.

4

You could shorten that formula:


=IF(A2<=300,210,IF(A2<=600,420,700+(TRUNC((A2-1)/1000)*350)))

(There’s an ambiguity over the result for 1001 – for consistency and simplicity, I have assumed that 1050 is right)

5

Good suggestion, but I think the …TRUNC((A2-1)… should actually be …TRUNC((A2-1001)…?

6

No, I tested it in an Excel spreadsheet, and my formula gave to right value for everything I put into it.

7

Just checked and I think you’re right – I made a mistake in mine (the $350 wasn’t charged until after $5000, which is probably not what Dog80 wanted).

8

Thanks a lot guys! I still can’t figure out how the formula works though :oops:

9

You could also do it with the LOOKUP function. Something like this, where the area value is in A1:

=LOOKUP(A1, surface_range, money_range) + INT(MAX(0, A1 - 4001) / 350) * 350

10

Typo - that should be

=LOOKUP(A1, surface_range, money_range) + INT(MAX(0, A1 - 4001) / 1000) * 350

11

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.

12

Do you mean you don’t know how to make it work in your spreadsheet? Or do you just want to know how the formula does what it does?