IF, AND, OR Help

[tenglish01]

Travel Template.xlsxSo this post is actually based on a previous post of mine... But now the issue has been complicated further.

Here's what I need:
AT costs $2.50/mile and has a $25 fee for loading wheelchairs (WC) or scooters (SCOOTER)
CD costs $5.00/mile and has a $75 fee for loading wheelchairs or scooters
There is no additional loading cost if the vechicle is SEDAN
VA will cost the same as CD
If column L says "RT" (Round Trip), I need to double the mileage I enter in column N
If column L says "OW" (One Way), the mileage in column N remains the same -- whatever value is entered.
There is a possibility that Columns A and C will be different carriers (either both CD, both AT, or a combination)

Can anyone help me with this? Here is the previous formula I was using:
=IF(AND(L7="RT",OR(M7={"WC","SCOOTER"})),150+5*N7,IF(AND(L7="OW",OR(M7={"WC","SCOOTER"})),75+5*N7,2.5*N7))

This does not take into account that AT and CD cost different amounts for mileage.

I attached the spreadsheet if that helps... THANK YOU!!!!

[tenglish01]

Is more information needed to help with this?

[FDibbins]

Can I suggest that you add some dummy data to your workbook - even if you have to calc it and enter it manually?

[tenglish01]

Can I suggest that you add some dummy data to your workbook - even if you have to calc it and enter it manually?

Travel Template_00.xlsx

Does that help? I need the "COST" Column to auto-populate with the other pertinent information.

[Pauleyb]

in O7:
=IF(A7="AT",2.5,5)*N7+(L7="RT")*IF(C7="AT",2.5,5)*N7+(M7<>"SEDAN")*(IF(A7="AT",25,75)+IF(C7="AT",25,75))

Drag down.

[protonLeah]

This one uses a lookup table and named ranges:

Please Login or Register  to view this content.

If column L = OW then CHOOSE returns 1 which causes it to calculate column A & C miles based on carriers, etc and adds, if RT then multipy Column A miles by 2...

[tenglish01]

This one uses a lookup table and named ranges:

Please Login or Register  to view this content.

If column L = OW then CHOOSE returns 1 which causes it to calculate column A & C miles based on carriers, etc and adds, if RT then multipy Column A miles by 2...

I've never used a lookup table before... how does this work, exactly?

[tenglish01]

in O7:
=IF(A7="AT",2.5,5)*N7+(L7="RT")*IF(C7="AT",2.5,5)*N7+(M7<>"SEDAN")*(IF(A7="AT",25,75)+IF(C7="AT",25,75))

Drag down.

This isn't working... With no values entered, it still gives me a value of 150.

[tenglish01]

Hey! It works! Perfect, thank you!

[protonLeah]

For instance, given:
INDEX(RateTable, MATCH($C7,CarrierList,0), 2),

1. The first parameter, RateTable, is the group of cells A1:E6 in the "lookup tables" tab.

2. The second parameter, MATCH($C7,CarrierList,0), calculates the row in the table to use. Here, it is looking for the carrier code ("AT" in C7) in the carrierlist (the first column of the lookup table). "AT" is found at row 3.

3. The third parameter, 2, specifies which column to return the value from.

So, in this case, INDEX returns the value from row 3, column 2, i.e., $5.00.

---

There are also two named ranges

"OW " (cell N1) and "RT" (M1), &
M1 holds value 2 and N1 holds value 1.

So, INDIRECT($L7), will take the type of trip in cells of column L (OW in the sample file) and use that as the named range to fetch the value from. Since L7 holds OW, INDIRECT looks in cell named "OW" and returns: 1.

Since INDIRECT will only return a 1 or 2, CHOOSE(INDIRECT($L7)..., picks from 2 formulas to calculate miles

If it's 1 then the first formula is evaluated (IN + OUT) , if 2 then evaluate the round trip formula (2 x IN):

CHOOSE( 1 or 2, (IN + OUT), (2 x IN))