Sumproduct problem

[bezmon1985]

hiya

having a little trouble with a Sumproduct formula. Below is what i would like to achieve

Cost of journey determine by length

0-29 Miles @ £1.7
30-50 Miles @ £1.6
51-80 Miles @ £1.4
80+ Miles @ £1.3

i have entered the formula (c21 being where i enter the mileage)

=SUMPRODUCT(--(C21>{0,29,30,50,51,80,81,9999}),--(C21-{0,29,30,50,51,80,81,9999}),{1.7,-0.1,-0.3,-0.4})

but it only seems to bring the correct amount upto 30 miles thereafter the result is incorrect!

Any help would be reatly appreciated

Many thanks for taking the time to look over my woes

Bezmon1985

 

[Dave Patton]

What is your calculation for 100 miles?
What would be the calculation for 29.5 miles?
This example assumes brackets slightly different; please adjust to the complete definition of the brackets.

Excel 2010
	A	B	C	D	E	F	G
1	100.00	150.90			Brackets	Rate	rDiff
2		150.90			0	1.7	1.7
3					29	1.6	-0.1
4		150.90			50	1.4	-0.2
5					80	1.3	-0.1
6
4a

Cell Formulas
Range	Formula
B1	= SUMPRODUCT(--(A1>E2:E5),A1-E2:E5,G2:G5)
B2	= SUMPRODUCT(--(A1>{0;29;50;80}),A1-{0;29;50;80},{1.7;-0.1;-0.2;-0.1})
B4	=SUMPRODUCT(--(A1>{0;29;50;80}),A1-{0;29;50;80},{1.7;1.6;1.4;1.3}-{0;1.7;1.6;1.4})