Autofill cells in Excel spreadsheet based on data from other cells

Occasional Visitor

Can anyone help me create a formula to autofill the 'Rate' cell? We're trying to get it to autopopulate based on the course (North or South), the day of the week (cell A1), the tee time (cell A6 and below), and the Type (cell J6 and below). The Type column is already formatted with a drop-down list of specific options: Non-Package, Stay n' Play, Group, and Day User.

 

The rates differ by North or South golf course, day of week, time of day, and type of booking. I'd just like to find a way to automate the rate entry if possible. Thank you!

 

Excel spreadsheet.png

1 Reply

@CamSDGolf You need to create one or more lookup table(s) in Excel. Difficult to say how, not knowing how exactly the fee structure looks like. For example, does every day/hour of the week/day have different rates or are you just differentiating between weekdays and week-ends? And perhaps for tee times before 8 AM, from 8 AM till 3 PM and after 3 PM. With regard to types, would these be based on a percentage of full greenfee? And for each course, could you say, for instance,  North = South +15%.

 

So, if there is some mathematical logic to how you calculate the rates, it should be fairly easy to calculate the rate based on a few variables and XLOOKUP (or VLOOKUP if you are using an older Excel version).

 

Otherwise, the lookup table(s) should be listing out every rate for every day/hour during the week, for every type and both courses. Doable, but a lot more work and the lookup functions may become a bit more complex.