Construction Opes - matching a rate to different sizes openings

[ClonguishOgabu]

Hi,

I have a Subcontractor who charges me different rates for various sizes opeings in walls.

Example;

the measured work is done on site and 0.15 is worth €2.42. what formula can put the€2.42 into the rate cells that fall between 0.1 & 0.2 etc

Opes size		Rate		Measured works	Nr	rate	Total
Ope < 0.001m2	each	€ 2.00		0.00​	6​		€ -
Ope < 0.01m2	each	€ 2.20		0.15​	2​		€ -
Ope < 0.1m2	each	€ 2.42		0.15​	5​		€ -
Ope < 0.2m2	each	€ 2.66		0.16​	1​		€ -
Ope < 0.3m2	each	€ 2.93		0.34​	6​		€ -
Ope < 0.4m2	each	€ 3.22		0.44​	2​		€ -
Ope < 0.5m2	each	€ 3.54		0.55​	5​		€ -
Ope < 0.6m2	each	€ 3.90		0.69​	1​		€ -
Ope < 0.7m2	each	€ 4.29		0.86​	6​		€ -
Ope < 0.8m2	each	€ 4.72		1.07​	2​		€ -
Ope < 0.9m2	each	€ 5.19		1.34​	5​		€ -
Ope > 1.0m2	m2	€ 5.71		0.88​	1​		€ -

 

[DRSteele]

If I understand correctly, this ought to do it.

Book1
	A	B	C	D	E	F	G	H
1	Hurdle	Rate			Measured works	Nr	rate	Total
2	0	€ 2.00	each		0.00	6	2.00	€ 12.00
3	0.001	€ 2.20	each		0.15	2	2.66	€ 5.32
4	0.01	€ 2.42	each		0.15	5	2.66	€ 13.30
5	0.1	€ 2.66	each		0.16	1	2.66	€ 2.66
6	0.2	€ 2.93	each		0.34	6	3.22	€ 19.32
7	0.3	€ 3.22	each		0.44	2	3.54	€ 7.08
8	0.4	€ 3.54	each		0.55	5	3.90	€ 19.50
9	0.5	€ 3.90	each		0.69	1	4.29	€ 4.29
10	0.6	€ 4.29	each		0.86	6	5.19	€ 31.14
11	0.7	€ 4.72	each		1.07	2	5.71	€ 12.22
12	0.8	€ 5.19	each		1.34	5	5.71	€ 38.26
13	0.9	€ 5.71	m2		0.88	1	5.19	€ 5.19
Sheet56

Cell Formulas
Range	Formula
G2:G13	G2	=VLOOKUP(E2,$A$2:$B$13,2)
H2:H13	H2	=IF(E2>1,E2*G2*F2,F2*G2)