Formula Errors

How to Calculate Weekly Creche Fees Using Excel Formulas

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to create an Excel formula that returns a weekly creche fee based on dropdown selections for a child's age, schedule, and number of days attended, while optionally capping the maximum hours at nine.

Product: Excel
Device & OS: not provided
Scenario: Building a dynamic daycare fee calculator using dropdown inputs for variable pricing criteria and capping daily or weekly hours.
Observed behavior: The goal is to successfully retrieve the correct fee from a matrix of variables and restrict the calculated hour outputs to a strict 9-hour limit.

Before you start

Before building your formulas, ensure you have set up a clean, organized reference table containing all possible combinations of ages, schedules, and attendance days to act as your data source.

Solution 1Recommended

Build a Reference Table and Apply Lookup Formulas

The most reliable way to calculate complex, multi-variable fee structures is to store all possible combinations in a reference table, use lookup functions to find the exact fee, and cap hours using the MIN function.

By using a structured reference table, you avoid writing excessively long and error-prone nested IF statements. This approach makes it easy to update fee prices in the future without changing the core formula.

Create a comprehensive reference table

On a separate sheet or off to the side, build a table listing every possible combination of age, schedule type, and number of days. Add a column next to these combinations for the corresponding fee.

Set up dropdown menus for inputs

In your main calculator area, use the Data Validation feature (found under the Data tab) to create dropdown lists for 'Age', 'Schedule', and 'Number of Days'. This ensures users can only select valid options that exist in your reference table.

Write the lookup formula

Use a combination formula like INDEX and MATCH, or XLOOKUP, to reference the user's dropdown selections and search your reference table for the matching row, returning the corresponding fee.

Cap calculated hours at a maximum of 9

If your worksheet also calculates hours and you need to enforce a 9-hour maximum limit, wrap the hours calculation in a MIN function. For example, click the hours cell and type: =MIN(your_hours_formula, 9).

Data Validation Tip: Using dropdown lists (Data Validation) for your input cells guarantees that the lookup formula will always find an exact match in your reference table, preventing #N/A errors.

Efficient Spreadsheet Management

Easily Calculate Fees and Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust formula support, including advanced lookup functions and data validation, allowing you to build dynamic fee calculators easily. It offers seamless compatibility with your existing workbooks.

1. Open or Create Your Workbook: Launch WPS Spreadsheet and open your existing Excel file, or start a new blank workbook to build your calculator from scratch.
2. Set Up Data Validation Dropdowns: Navigate to the 'Data' tab and click 'Data Validation' to easily create dropdown menus for Age, Schedule, and Days.
3. Insert Your Lookup and Cap Formulas: Click on your target cell and seamlessly insert your INDEX/MATCH formulas. Use the =MIN() function to confidently cap your calculated hours.

Fully compatible with Microsoft Excel (.xlsx) formats, features, and complex formulas.Supports advanced lookup functions like VLOOKUP, INDEX, and MATCH for complex fee calculation.Lightweight, fast, and completely free to use for your daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

How do I cap a calculated number at a specific maximum in Excel?

You can use the MIN function to cap a value. For example, to cap a value at 9, use =MIN(A1*B1, 9). Excel will evaluate your calculation and return it, unless it exceeds 9, in which case it will simply return 9.

What is the best formula for looking up data with multiple criteria?

When looking up data based on multiple criteria (like Age AND Schedule AND Days), combining INDEX and MATCH is highly effective. You can also use XLOOKUP or create a helper column in your reference table that concatenates the criteria into a single string for a standard VLOOKUP.

Why is my lookup formula returning an #N/A error?

The #N/A error occurs when the lookup formula cannot find an exact match for your criteria in the reference table. Ensure that there are no trailing spaces in your text, and verify that your dropdown list inputs exactly match the spelling and formatting of the data in your reference table.

Excel & Data Problems