breakpoint calculation

O

Oliver

The following is a presentation of rate table:
<45kg +45KG +100KG +300KG +500KG +10000KG
$9.5 $8.5 $7.5 $4.5 $3.5 $1.5

It reads if weight is smaller than 45kg then we charge $9.5 per kg; if
weight is between 45 and 100kg then we charge $8.5 and so on; is there
an easy way to write a formula to calculate the total charge based on
the weight?
I could use if(weight<45,9.5*weight,if(and(weight>45,weight<100),
8.5*weight, etc etc) but it will be a long formula...
i think it must have an easy way to do this...
 
R

Ron Rosenfeld

The following is a presentation of rate table:
<45kg +45KG +100KG +300KG +500KG +10000KG
$9.5 $8.5 $7.5 $4.5 $3.5 $1.5

It reads if weight is smaller than 45kg then we charge $9.5 per kg; if
weight is between 45 and 100kg then we charge $8.5 and so on; is there
an easy way to write a formula to calculate the total charge based on
the weight?
I could use if(weight<45,9.5*weight,if(and(weight>45,weight<100),
8.5*weight, etc etc) but it will be a long formula...
i think it must have an easy way to do this...

If your Rate Table is in a horizontal alignment, as you show, then HLOOKUP will
do the job.

Set up the table someplace as follows:

0 45 100 300 500 10000
9.5 8.5 7.5 4.5 3.5 1.5


NAME it RateTable.

Then, with your weight in A1, use this formula:

=HLOOKUP(A1,RateTable,2)*A1
--ron
 
R

RagDyeR

Try this:

=LOOKUP(A1,{0,46,101,301,501,10001;9.5,8.5,7.5,4.5,3.5,1.5})*A1
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


The following is a presentation of rate table:
<45kg +45KG +100KG +300KG +500KG +10000KG
$9.5 $8.5 $7.5 $4.5 $3.5 $1.5

It reads if weight is smaller than 45kg then we charge $9.5 per kg; if
weight is between 45 and 100kg then we charge $8.5 and so on; is there
an easy way to write a formula to calculate the total charge based on
the weight?
I could use if(weight<45,9.5*weight,if(and(weight>45,weight<100),
8.5*weight, etc etc) but it will be a long formula...
i think it must have an easy way to do this...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top