Below is our feescale matrix for purchases. If the feescale number is in a given field (for the sake of this formula I will call if Feescale), the excel formula would be:
=if(property price<200001,if(feescale=1,549,if(feescale=2,649,if(property price<300001,649,if(property price<400001,749,if(property price<500001,849,949))))))
This would cover the first 5 bands for feescales 1 and 2. For any other feescale or any other band in feescales 1 and 2, the above formula would return 949. The formula works on the following basis:
=if(logical test,value if test is true,value if test is false)
As shown in the above example, the value if true or the value if false can be replaced by more “=if” functions
| Property Price |
Feescale |
||||
| From |
To |
1 |
2 |
3 |
4 |
| 0 |
200,000 |
549 |
649 |
649 |
649 |
| 200001 |
300000 |
649 |
649 |
649 |
649 |
| 300001 |
400000 |
749 |
749 |
649 |
649 |
| 400001 |
500000 |
849 |
849 |
749 |
649 |
| 500001 |
750000 |
949 |
949 |
749 |
749 |
| 750001 |
|
1049 |
1049 |
749 |
749 |