varible calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a calculation for haulage/trucking charges

1-300 kilos £ 30
300-400 kilos £ 35
400-500 kilos £ 40

What formula could I use to accomplish this??
 
assuming that you mean:

1-299
300-399
400-

then try:

=LOOKUP(A1,{0,300,400},{30,35,40})

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
if the weight is in cel A1 you could try
=IF(A1>300,IF(AND(A1>300,A1<400),35,40),30)
you will need to change it slightly as you have 2 values for 300 & 400
kilo's. You might also want some validation to prevent the cell having a
value over 500 kilo's
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
This formula presumes that you have a cell named weight and that weight is
not 0, and that any weight over 400 kilos is £ 40. You can make it more
elaborate. For a few more weights, you could do a table lookup.

=IF(weight<=300,30,IF(weight<=400,35,40))
 
First I think you have a couple of typos. You can't have two rates for 300 or
400

Maybe you mean

1-300
301-400
401-500

or do you mean?

1-299
300-399
400-500

Will there ever be fractions like 299.96 kg?

Will there ever be more than 500 kg?

If so, would the max be £40

=LOOKUP(A1,{1,301,401,501},{30,35,40,40})

Try the above............adjust ranges as needed.


Gord Dibben MS Excel MVP
 
Sorry, didn't pay enough attention. The formula will not work. Your criteria
need clarification.

1-300 kilos is £ 30, 300 - 400 kilos is £ 35. You can't have that. 300 kilos
is both £ 30 and £ 35 and 400 kilos is both £ 35 and £ 40.
 
many thanks
--
kinsey


Sandy Mann said:
assuming that you mean:

1-299
300-399
400-

then try:

=LOOKUP(A1,{0,300,400},{30,35,40})

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
works a treat! many thanks
--
kinsey


Gord Dibben said:
First I think you have a couple of typos. You can't have two rates for 300 or
400

Maybe you mean

1-300
301-400
401-500

or do you mean?

1-299
300-399
400-500

Will there ever be fractions like 299.96 kg?

Will there ever be more than 500 kg?

If so, would the max be £40

=LOOKUP(A1,{1,301,401,501},{30,35,40,40})

Try the above............adjust ranges as needed.


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top