Specific Calculation

[mrkris1982]

Column J is # of Days
Column M is a validation list that have about 6 options; one of those is CAB
Column Q is multiplying the # of days by the $ amount associated with CAB via a lookup on another sheet

=IFERROR(VLOOKUP(M53,'Valid Values'!$F$2:$G$7,2,FALSE)*[@[No. Of Attendees]]*[@['# of Days]],"")

Problem is, if 3 or more days is selected in J AND Cab is selected in M, I want the calculation in Q to multiply by 2 days (ie, no more than 2 days of taking a cab)

Here is the full chart of options and costs:

Travel Means	Travel Means Costs
Rental Car	65
Pool Car	55
Train	45
Cab	40
Other	50
No	0

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

Whats the most efficient way to achieve this?

 

[Yongle]

Try this to get the number of days

=IFERROR(VLOOKUP(M53,'Valid Values'!$F$2:$G$7,2,FALSE)*[@[No. Of Attendees]]*MIN(2,[@['# of Days]]),"")

 

[Yongle]

If 2 day restriction only applies when M53 = CAB

=IFERROR(VLOOKUP(M53,'Valid Values'!$F$2:$G$7,2,FALSE)*[@[No. Of Attendees]]*IF(M53="CAB",MIN(2,[@['# of Days]]),[@['# of Days]]),"")