Problem with Minimum Price in IIf argument

[bellevue]

I let holiday properties on different Holiday Parks; one of the Parks (let's call it "ABC Holiday Park") has a site pass fee which must be collected with the booking.

The charge is £12.00 for 1-4 people but for the 5th, 6th, 7th or 8th person (if applicable) it's an additional £3.00 per person.

I'm trying to write an IIf argument for this formula; however, I want the argument to return a value of £0.00 for all other Holiday Parks, that is those that don't charge a site pass fee.



So far, I've created the following:

=IIf([Holiday Park]="ABC Holiday Park",IIf([Occupancy]<=4,"£12.00",IIf([Occupancy]>=5,[Occupancy]*3,"£0.00")))

It works fine as far as all bookings for ABC Holiday Park are concerned but doesn't return a £0.00 for those non-site pass fee parks.

Can anyone help please ?

[CGM3]

The way your formula is set up, the "£0.00" can only be returned IF the Park is ABC Holiday Park AND the Occupancy is >=5 AND the Occupancy is also NOT >= 5 (which is about as possible as one of Escher's endless stairways being actually built.)

Either of these should do the trick (my preference is the second one, which eliminates non-ABC Holiday Parks right off the bat):

=IIf([Holiday Park]="ABC Holiday Park",IIf([Occupancy]<=4,"£12.00",[Occupancy]*3),"£0.00")

=IIf([Holiday Park]<>"ABC Holiday Park","£0.00",IIf([Occupancy]<=4,"£12.00",[Occupancy]*3))

[pbaldy]

You appear to have the 0 in the False argument of the >= 5 IIf, not the "ABC Holiday Park" IIf (which has no False argument).

[orange]

How about

=IIf([Holiday Park]<>"ABC Holiday Park","£0.00",
  IIf([Occupancy]<=4,"£12.00",IIf([Occupancy]>=5,[Occupancy]*3,"£0.00")))

You didn't say what the fee was for >8 occupants, but your expression has it at "£3.00"/person.

You have text values for Cost/fee, is that really working?

[Robeen]

IIf([Holiday Park]="ABC Holiday Park",IIf([Occupancy]<=4,"£12.00",[Occupancy]*3),"£0.00")

. . . while I was working on this Paul & Orange posted . . . sorry. I think I ended up with what Paul pointed out.

[bellevue]

Thanks ever so much CGM3, pbaldy, orange and particularly Robeen, that's superb and woprks fine. I think my head was getting all "kafuddled" after so many attempts !!