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

 

[lrobbo314]

How about this...

=LOOKUP(C21,{0,29,50,80},{1.7,1.6,1.4,1.3})*C21

 

[bezmon1985]

How about this...

=LOOKUP(C21,{0,29,50,80},{1.7,1.6,1.4,1.3})*C21

Hiya

thanks for help, for some reason the maths is correct up to 27 miles and thereafter goes astray!?!?!

thanks again

Bezmon1985

 

[lrobbo314]

Small adjustment. I doubled checked. Everything seems to match now.

=LOOKUP(C21,{0,30,51,80},{1.7,1.6,1.4,1.3})*C21

 

[jtakw]

Hi,

If I understand correctly, don't think SUMPRODUCT was the correct approach to begin with, and the LOOKUP will only give a linear result.
I'm assuming what you have is a Tiered Charge/Cost Table, this is one approach, I may be wrong in my assumption:
Need adjustment to my formula, post back soon.

 

[jtakw]

Hi,

If I understand correctly, don't think SUMPRODUCT was the correct approach to begin with, and the LOOKUP will only give a linear result.
I'm assuming what you have is a Tiered Charge/Cost Table, this is one approach, I may be wrong in my assumption:
Need adjustment to my formula, post back soon.

Here you go:

Book1
	B	C	D
15	From	To	Cost
16	0	29	1.7
17	30	50	1.6
18	51	80	1.4
19	81		1.3
20
21		81	126.2
Sheet16

Cell Formulas
Range	Formula
D21	=SUM(MEDIAN(B16,C16,C21)*D16,MEDIAN(0,C17-B17+1,C21-C16)*D17,MEDIAN(0,C18-B18+1,C21-C17)*D18,MAX(0,C21-C18,0)*D19)

 

[lrobbo314]

I see what you're saying jtakw. If that is the case, here is another way of doing it that will get the same result.

=SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&C21)),{0,30,51,81},{1.7,1.6,1.4,1.3}))

 

[bezmon1985]

hi all

thanks for all your help, have manged to get a result using LOOKUP by creating a manual table with all result listed entered manually

cheers again

Bezmon1985

 

[jtakw]

You're welcome, welcome to the forum, glad you sorted it out.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	81	126.5			0	1.7	1.7
2		126.5			30	1.6	-0.1
3					51	1.4	-0.2
4					80	1.3	-0.1
5
4a

Cell Formulas
Range	Formula
B1	= SUMPRODUCT(--(A1>E1:E4),A1-E1:E4,G1:G4)
B2	= SUMPRODUCT(--(A1>{0;30;51;80}),A1-{0;30;51;80},{1.7;-0.1;-0.2;-0.1})