Formula Needed

[gsaenz]

I am a truck driver and I set up a spreadsheet that calculates all m
milage, expenses and income. In one sheet I have date, destination
miles, tons, rate per ton, total wages, start miles, etc.
My problem is that depending how many miles I do in one day I get paye
a different rate. IE: 1-10 miles has a rate of $1.6940 per ton. 1
miles has a rate of $1.7270 per ton (Usually I do about 25 tons) 1
miles has a rate of $1.7590 per ton, And so on and so forth. I need
"if then" statement or formula that will, depending on the miles
enter, choose the correct rate and thereof making the correct formul
and giving an income. In one day I can do up to 800 miles. I am aware
will have to input all the rates for a reference, I already starte
doing that, Also I need the sheet to be small so it can be printed out
Any help is greatly appreciated

 

[Norman Harker]

Hi gsaenz!

Use VLOOKUP.

As a brief example

A1 contains 11
K1:J3 contains the table

0 1.694
11 1.727
12 1.759

B1 contains the formula:

=A1*VLOOKUP(A1,$K$1:$L$4,2)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Mike A]

Create your rate table on a separate sheet, say sheet2. The first
column will have the number of miles, or the low end of the range in
the case of the same rate for a range of miles ( 1 would represent
1-10 miles when 11 is the value in the next row for miles). The second
column will have the rate.

1 2

A 1 1.694
B 11 1.727
C 12 1.759

And so on...

Now select the whole range (both columns), and name it 'rates' or
whatever you want. To do this: above the spreadsheet grid, to the
left of the formula bar, click in the name box. It should show the
upper-left cell of your selection. Type the range name here, and
press enter.

Now, in your other sheet, use this formula in your rate cells:

=vlookup(A3,rates,2)

Replace A3 with the address of your cell with your miles in it, and
'rates' with whatever name you used for your range on the second
sheet.

This formula looks through the table named 'rates' for the value in
cell A3 and returns the corresponding value from the second column in
the table.

By putting this table on a separate sheet, it will not affect the
printing of your other sheet.

Good luck, and drive safely!!


Mike

I am a truck driver and I set up a spreadsheet that calculates all my
milage, expenses and income. In one sheet I have date, destination,
miles, tons, rate per ton, total wages, start miles, etc.
My problem is that depending how many miles I do in one day I get payed
a different rate. IE: 1-10 miles has a rate of $1.6940 per ton. 11
miles has a rate of $1.7270 per ton (Usually I do about 25 tons) 12
miles has a rate of $1.7590 per ton, And so on and so forth. I need a
"if then" statement or formula that will, depending on the miles I
enter, choose the correct rate and thereof making the correct formula
and giving an income. In one day I can do up to 800 miles. I am aware I
will have to input all the rates for a reference, I already started
doing that, Also I need the sheet to be small so it can be printed out.
Any help is greatly appreciated.

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Mike A]

Create your rate table on a separate sheet, say sheet2. The first
column will have the number of miles, or the low end of the range in
the case of the same rate for a range of miles ( 1 would represent
1-10 miles when 11 is the value in the next row for miles). The second
column will have the rate.

A B

1 1 1.694
2 11 1.727
3 12 1.759

And so on...

Now select the whole range (both columns), and name it 'rates' or
whatever you want. To do this: above the spreadsheet grid, to the
left of the formula bar, click in the name box. It should show the
upper-left cell of your selection. Type the range name here, and
press enter.

Now, in your other sheet, use this formula in your rate cells:

=vlookup(C1,rates,2)

Replace C1 with the address of your cell with your miles in it, and
'rates' with whatever name you used for your range on the second
sheet.

This formula looks through the table named 'rates' for the value in
cell C1 and returns the corresponding value from the second column in
the table.

By putting this table on a separate sheet, it will not affect the
printing of your other sheet.

Good luck, and drive safely!!


Mike

I am a truck driver and I set up a spreadsheet that calculates all my
milage, expenses and income. In one sheet I have date, destination,
miles, tons, rate per ton, total wages, start miles, etc.
My problem is that depending how many miles I do in one day I get payed
a different rate. IE: 1-10 miles has a rate of $1.6940 per ton. 11
miles has a rate of $1.7270 per ton (Usually I do about 25 tons) 12
miles has a rate of $1.7590 per ton, And so on and so forth. I need a
"if then" statement or formula that will, depending on the miles I
enter, choose the correct rate and thereof making the correct formula
and giving an income. In one day I can do up to 800 miles. I am aware I
will have to input all the rates for a reference, I already started
doing that, Also I need the sheet to be small so it can be printed out.
Any help is greatly appreciated.

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[gsaenz]

Thanks for the fast help.

=vlookup(C1,rates,2)

I understand what c1 and rates mean, What does the number 2 mean or do?

 

[gsaenz]

thanks for the fast help

=A1*VLOOKUP(A1,$K$1:$L$4,2)

how do I make it look for the information on another sheet?

=A1*VLOOKUP(A1,rates,$K$1:$L$4,2) like this??

 

[Norman Harker]

Hi gsaenz!

The third argument in the VLOOKUP "tells" Excel to take the item in
the second column of the VLOOKUP table. These tables could have many
columns with different columns being required for different purposes.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi gsaenz!

Perhaps the best way is to name the range that contains your rates.

Select the table of rates
Type FreightRates in the name box immediately to the left of the
formula bar
Press Enter.

Your formula then becomes:

=A1*VLOOKUP(A1,FreightRates,2)

There are other ways but I'd recommend strongly that you get into the
habit of naming cells and ranges as it makes formulas so much more
meaningful when you come back to them.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[gsaenz]

Ok I got it to work. Thanks so much. I had to use this formula
"=vlookup(a1,rates,2)"
I dont have to multiply it yet. that is done later on. Thanks again for
all the help. That saves me a lot of time since my son gave me a Palm
Pilot for xmas. I know some coding but not as good as you guys.

Thank you and a happy new year
Gabriel Saenz

 

[Norman Harker]

Hi Gabriel!

Thanks for thanks is always appreciated and shows Google Searchers and
Forum users that the solution works "as advertised".

See you back next time you have a problem

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.