How to apply IF statement to multiple Start and End Ranges - Property Valuation

[fakeafaik]

Hello,[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 606"]
<tbody>[TR]
[TD="colspan: 2"]Cost of Construction[/TD]
[TD]£50,000,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start_Range[/TD]
[TD]End_Range[/TD]
[TD]Adjustment (%)[/TD]
[TD]Contract Size Adjustment[/TD]
[/TR]
[TR]
[TD]£0[/TD]
[TD]£500,000[/TD]
[TD]0.100[/TD]
[TD]GREEN CELL[/TD]
[/TR]
[TR]
[TD]£500,001[/TD]
[TD]£585,000[/TD]
[TD]0.090[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£585,001[/TD]
[TD]£670,000[/TD]
[TD]0.080[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£670,001[/TD]
[TD]£750,000[/TD]
[TD]0.075[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£750,001[/TD]
[TD]£1,000,000[/TD]
[TD]0.060[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£1,000,001[/TD]
[TD]£1,250,000[/TD]
[TD]0.050[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£1,250,001[/TD]
[TD]£1,500,000[/TD]
[TD]0.040[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£1,500,001[/TD]
[TD]£1,750,000[/TD]
[TD]0.030[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£1,750,001[/TD]
[TD]£2,000,000[/TD]
[TD]0.020[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£2,000,001[/TD]
[TD]£2,500,000[/TD]
[TD]0.010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£2,500,001[/TD]
[TD]£2,999,999[/TD]
[TD]0.005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£3,000,000[/TD]
[TD]£3,000,000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£3,000,001[/TD]
[TD]£3,500,000[/TD]
[TD]-0.005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£3,500,001[/TD]
[TD]£4,000,000[/TD]
[TD]-0.010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£4,000,001[/TD]
[TD]£4,500,000[/TD]
[TD]-0.015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£4,500,001[/TD]
[TD]£5,000,000[/TD]
[TD]-0.020[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£5,000,001[/TD]
[TD]£5,500,000[/TD]
[TD]-0.025[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£5,500,001[/TD]
[TD]£6,000,000[/TD]
[TD]-0.030[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£6,000,001[/TD]
[TD]£6,500,000[/TD]
[TD]-0.035[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£6,500,001[/TD]
[TD]£7,000,000[/TD]
[TD]-0.040[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£7,000,001[/TD]
[TD]£7,500,000[/TD]
[TD]-0.045[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£7,500,001[/TD]
[TD]£8,000,000[/TD]
[TD]-0.050[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£8,000,001[/TD]
[TD]£8,750,000[/TD]
[TD]-0.055[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£8,750,001[/TD]
[TD]£9,500,000[/TD]
[TD]-0.060[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£9,500,001[/TD]
[TD]£10,250,000[/TD]
[TD]-0.065[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£10,250,001[/TD]
[TD]£11,000,000[/TD]
[TD]-0.070[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£11,000,001[/TD]
[TD]£12,500,000[/TD]
[TD]-0.075[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£12,500,001[/TD]
[TD]£14,000,000[/TD]
[TD]-0.080[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£14,000,001[/TD]
[TD]£15,500,000[/TD]
[TD]-0.085[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£15,500,001[/TD]
[TD]£17,000,000[/TD]
[TD]-0.090[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£17,000,001[/TD]
[TD]£20,000,000[/TD]
[TD]-0.095[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£20,000,001[/TD]
[TD]£1,000,000,000[/TD]
[TD]-0.100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I've read through many help pages trying to solve my problem, but to be honest I don't think I am correctly wording what I am trying to do.

I've attached a picture to help illustrate my query. The purpose of this part of the spreadsheet is to select an appropriate contract size adjustment for a property contractor's valuation.

The top cell contains a cost of construction (coc), which is variable depending on other figures above this.

Taking the first row as an example, if the coc is between £0 and £500,000 then the coc should be adjusted by 0.100. I would like the green cell to read +10% (or even 10%).

Taking a middle row as an example, if the coc is £4,200,000 then the coc should be adjusted by -0.015. I would like the green cell to read -1.5%.

Taking the bottom row as an example, if the coc is over £20,000,000 then the coc should be adjusted by -0.100. I would like the green cell to read -10%.

 

[jeffreybrown]

Just make D2=C2 and then change the format to %

 

[fakeafaik]

Thanks for the reply, but I am trying to make to green cell select a figure based on the different ranges.

For example, if the Cost of Construction is £5,200,000 then the green cell will select -0.025 from the adjustment list. I then know I can format it to a percentage.

 

[jeffreybrown]

So everything in the "Green Cell" revolves around the CoC? If so...

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Cost of Construction​

[/td][td]

​

[/td][td]

£5,200,000​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

3​

[/td][td]

Start_Range​

[/td][td]

End_Range​

[/td][td]

Adjustment (%)​

[/td][td]

Contract Size Adjustment​

[/td][td]

​

[/td][/tr]

[tr][td]

4​

[/td][td]

£0​

[/td][td]

£500,000​

[/td][td]

0.1​

[/td][td]

-2.5%​

[/td][td]

D4:=LOOKUP($C$1,$A$4:$B$35,$C$4:$C$35)​

[/td][/tr]

[tr][td]

5​

[/td][td]

£500,001​

[/td][td]

£585,000​

[/td][td]

0.09​

[/td][td]

-2.5%​

[/td][td]

​

[/td][/tr]

[tr][td]

6​

[/td][td]

£585,001​

[/td][td]

£670,000​

[/td][td]

0.08​

[/td][td]

-2.5%​

[/td][td]

​

[/td][/tr]
[/table]

 

[fakeafaik]

Thanks Jeff. I can see you have that working in your spreadsheet, but if I put that into my spreadsheet it doesn't work.

I'm a pretty basic excel user, so do I need to format the cell to insert a LOOKUP formula? Also, I don't understand where the £0.67 , £23.55 come from...

I really appreciate your help.

Kind regards,

Michael

 

[jeffreybrown]

Hi Micheal,

Sorry I messed up the formula. Should be =LOOKUP($C$1,$A$4:$A$35,$C$4:$C$35)

I'm a pretty basic excel user, so do I need to format the cell to insert a LOOKUP formula?

You don't have to format the cell of the LOOKUP formula, you just plugged it in the cell.

Also, I don't understand where the £0.67 , £23.55 come from...

I don't know where those numbers come from either. I don't seem them in the data you posted.

 

[Sttil]

Hi there,

Do you actually want "The Green Cell" on each row, or only on the same row as 0-500000 (as in your example), or only on the row for which the CoC is applicable (i.e. a CoC of 550,000 to generate "the green cell" on second row)?

Maybe it's enough to get the percentage on a completely separate cell named "Adjustment" or something like that?

 

[fakeafaik]

Thanks folks.

I don't need "The Green Cell" on each row. I only need it to tell me what percentage adjustment I should use for the Cost of Construction.

Essentially the number in the adjustment column is the correct contract size adjustment. But the Cost of Construction will be changing throughout. So I would like the Contract Size Adjustment % to automatically adjust when the Cost of Construction is changed. I hope that makes sense.

 

[skeeve]

If I understand you right - this can be done very simply with a "loose" vlookup. By using "approximate match" versus "exact match", if it doesn't find the exact value you entered, it returns the next lowest value. This is useful because then all we are concerned with is asking what is the next starting point for a new adjustment %.

You don't even need the "End Range"... just a list of when you want new breakpoints/Start Ranges... Hows this... I've added multiple lines to show how it works with various contract values but there's nothing stopping you from just having one.

 

[fakeafaik]

That works! Thank you so much Skeeve. Out of interest, I can see what E4,A:B is doing, but what does the '2' do in that vlookup?

This should definitely do me for now. The next step is for me to try & do another version of this, but with more accurate interpolation between the values. I've spoke to a colleague who said it is quite tricky because it isn't a straight line interpolation, but a stepped rise.

I really appreciate the help on this part though. Many thanks again folks!