IF function Please HELP!!

rawnaah

New Member
Joined
Apr 18, 2013
Messages
10
How would one use IF function to determine the following please help...Thanks


SECONDARY TRANSPORT RATE:251 TO 400 KM & >400 $ 24 PER MT PER KM
SECONDARY TRANSPORT RATE:101 TO 250 KM $25 PER MT PER KM
SECONDARY TRANSPORT RATE:31 TO 100 KM $25 PER MT PER KM
SECONDARY TRANSPORT RATE:11 TO 30 KM $25 PER MT PER KM
SECONDARY TRANSPORT RATE:<=10 $600 PER MT

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Your question is not exactly clear..

Can you explain in words what you're trying to do (along with the sample you posted)


Best guess is something like a lookup
=LOOKUP(A1,{0,11,251},{600,25,24})
 
Upvote 0
I'm having trouble following your table, and not sure the logic you want to use - are you using multiple rates for each thing - as in, 0-10 has $600, then add an additional $25 for KMs 11 to 30, then an additional $25 for KMs 31 to 100?

That's the only way I can make sense of the lowest tier being the higher $ amount... It also makes the solution vastly different than a simple IF statement.
 
Upvote 0
Re: How would IF function be used here,Please HELP!

a vlookup would work like this:

Excel 2010
ABC
1<=10 $0600 PER MT PER KM
211 TO 30 KM $1125 PER MT PER KM1
331 TO 100 KM $3125 PER MT PER KM2
4101 TO 250 KM $10125 PER MT PER KM3
5251 TO 400 KM & >400 $25124 PER MT PER KM
6
7
830024 PER MT PER KM
9150
1075
1120
128
Sheet1
Cell Formulas
RangeFormula
B8=VLOOKUP(A8,B1:C5,2)
 
Upvote 0
Thanks for your responses,i'll see what i can to best clearly explain..

Actually,i wanted to use one IF function to provide an output on the following arguments. For 10km or less=$600,between 11km to 30km=$25,between 31km to 100km=$25,between 101km to 250km=$25 and between 251km to 400km & more= 24.

I am not sure i've made enough clarity there.Sorry! thanks again
 
Upvote 0
What value are you trying to compare with these ranges?
A value in a Cell? Which Cell?
Does that cell contain just a number, or is it a text string like 101km?

These 3 statements are redundant
between 11km to 30km=$25,between 31km to 100km=$25,between 101km to 250km=$25
is the same as saying
between 11km to 250km=$25


If you put a number (JUST a number like 50 instead of 50km) in a cell, say A1
Then this formula does what you're looking for

=LOOKUP(A1,{0,11,251},{600,25,24})
 
Last edited:
Upvote 0
Jonmo1 i see your point about redundancy in those 3 statements,but that is how the data is.As for the value in the cell that am trying to compare? yeah it does contain km.i'll see if i can post the data as it is in my spreadsheet.
 
Upvote 0
Re: How would IF function be used here,Please HELP!

Thanks tex but it is only applying up to cell B11(in your example),the remaining cells (from B12 up to down containing values are showing errors e.g# N/A)..
 
Upvote 0
Re: How would IF function be used here,Please HELP!

Thanks tex but it is only applying up to cell B11(in your example),the remaining cells (from B12 up to down containing values are showing errors e.g# N/A)..

Lock the range B1:C5 as follows:

=VLOOKUP(A8,$B$1:$C$5,2)
 
Upvote 0
Re: How would IF function be used here,Please HELP!

Lock the range B1:C5 as follows:

=VLOOKUP(A8,$B$1:$C$5,2)


Ok now a cell in column A with value 254 gave an output of 600 instead of 24(as per 251 to 400km> 400) range,thanks.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top