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:

[TABLE="width: 199"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Travel
Means[/TD]
[TD]Travel Means Costs[/TD]
[/TR]
[TR]
[TD]Rental Car[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Pool Car[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Train[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Cab[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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]]),"")