• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Formula

E

EMMAQUEENS

New Member
I am looking to do a formula in excel to work out what rate to pay my sub-contractors. This is determined by the mileage from their home address to their destination.

e.g. Birmingham to Manchester = 85 miles

Rates are based on mileage up to 100, 125, 150, 175 and so on.

I want to type the engineer name in column A, home post code in column B and number of miles in column C. I then have the mileage in the following columns (D = 100, E = 125, F = 130 and so on). At the end there is then a total column.

I have tried the IF formula, IF cell C is lower than 100. How do i do IF cell C is between 0-100, 101-125, 126-150. Instead of lower than/higher than?
 
Hi Emma,

Welcome to chandoo.org forum.

Can you tell say Column A is engineer name , Column B is post code, Column C is number of miles than what are you expecting from your formula?

Can you upload a sample file with some examples .

Regards,
 
Hi,

you can make a table of rates and mileage and use VLookup to find with last parameter as TRUE. This will consider anything upto 100 as what is given against 100, like wise anything between 101 - 125 will pick up value given against 125, and so on.

If this is not what you are looking then, please share sample file.

Regards,
Prasad DN
 
Hi Emmaqueens,

See enclosed file if this is what you wanted.

I have made some changes to the data layout (instead on mileage in separate columns i.e. D, E, F etc. it is entered in column C corresponding to the respective name. Rate per mile (Column D) is calculated from the table in G2:H6 as also suggested by @prasaddn in above post.

While using the vLookup with "Range Lookup" as 1 i.e. TRUE it needs to be kept in mind that the "Table Array" needs to be sorted. It will not give desired results if used on unsorted "Table array".

I have used Currancy format as £ taking a hint from your "e.g. Birmingham to Manchester = 85 miles". you may change it as per your need.
Thanks
 

Attachments

You must log in or register to reply here.
Back
Top