LOOKUP to calculate cubic metreage

[wirra]

Hello,

I am endeavouring to come up with a formula in column M to calculate the cubic meterage for a range of furniture products. I have included the answer I need in column M. Cells B1:K1 represent the number of units while column L is the amount of stock. Some products are stackable in different configurations while others are not. If there is more than 10 in stock (column L) then the calculation will be the number 10's x column K plus the value for the remainder. For example, the calculation for product P298569 in row 3 would be (16 x 6)+4.2=100.2 cubic metres.

I have tried to use nestable IF statements with some success but I believe a lookup statement might be better but I can't find a solution.

Hope someone can help.

Thanks

Bob

Cubic Meterage Test.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ProductCode	1	2	3	4	5	6	7	8	9	10	StockonHand	CubicMeterage
2	P298576	0.25	0.5	0.75	1	1.25	1.5	1.75	2	2.25	2.5	20	5
3	P298569	0.6	1.2	1.8	2.4	3	3.6	4.2	4.8	5.4	6	167	100.2
4	P298457	0.05	0.1	0.15	0.2	0.25	0.3	0.35	0.4	0.45	0.5	6	0.3
5	P289098	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09	0.1	24	0.24
6	P288601	0.2	0.4	0.6	0.8	1	1.2	1.4	1.6	1.8	2	240	48
7	P288559	0.04	0.04	0.08	0.08	0.12	0.12	0.16	0.16	0.2	0.2	18	0.36
8	P288335	0.85	0.85	1.7	1.7	2.55	2.55	3.4	3.4	4.25	4.25	47	20.4
Sheet1

 

[Richard Schollar]

Hi Bob

See the following - formula in M2 is copied down as far as required.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ProductCode	1	2	3	4	5	6	7	8	9	10	StockonHand	CubicMeterage
2	P298576	0.25	0.5	0.75	1	1.25	1.5	1.75	2	2.25	2.5	20	5
3	P298569	0.6	1.2	1.8	2.4	3	3.6	4.2	4.8	5.4	6	167	100.2
4	P298457	0.05	0.1	0.15	0.2	0.25	0.3	0.35	0.4	0.45	0.5	6	0.3
5	P289098	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09	0.1	24	0.24
6	P288601	0.2	0.4	0.6	0.8	1	1.2	1.4	1.6	1.8	2	240	48
7	P288559	0.04	0.04	0.08	0.08	0.12	0.12	0.16	0.16	0.2	0.2	18	0.36
8	P288335	0.85	0.85	1.7	1.7	2.55	2.55	3.4	3.4	4.25	4.25	47	20.4
Sheet1

 

[wirra]

Thanks Richard. That's fantastic.

Bob