Advice Needed: Performing complicated nested if calculation

Is it possible to give a small sample of your data and expected outcomes?

Rather than thinking in terms of nested "if" type statements, I strongly recommend you think in terms of "flow", i.e. "given data that looks like X, I should treat it in Y way."  Two major ways in excel to express a thought like that are the "ifs" formula and the "switch" formula, depending upon your particular circumstance.  Think of the "ifs" being what you use when the data you are considering is not simply different values of the same variable name.  Because you mention multiple sources/types of data that would be relevant to how you perform your calculations, it is unlikely that a switch would provide any conceptual benefits or readability enhancements.

For instance, a general description for your various scenarios using a flow-type approach might be "When these condition are met, apply this formula.  When those conditions are met, apply that formula.  When a third set of conditions are met, apply a third formula, and when none of these specified scenarios are present, carry on using a fourth formula."

So now you just need to figure out what your conditions are and what you want to do (which is where you started).

Question 1: Where is the data related to your conditions?

If, for instance, you have a data table which includes independent variables, e.g. the date rate A changed, the date rate B changed, and no relation between A and B or the dates of change, things are different than if you have a complicated relationship between A and B, the dates of change, and the information is all over the place.

In the simplest case, you might have a data table with headers like "rate_change_date", "rate_type", "new_rate" which includes all rates from inception of your data through the end of your data.  In such a case, you might answer the question "Did a rate change during the period?" by an uncomplicated date filter.  

You should try to focus on the question of your conditions and structure data independent of your questions about what/how to do a calculation so that you have a clear logical structure in place that makes sense and is usable regardless of the calculation you end up needing to do.

For instance:

get_rate_changes_during_period = LAMBDA(period_start, period_end, 
    FILTER(
        rate_change_table, 
        (rate_change_table[date] <= period_end) * 
        (rate_change_table[date] >= period_start), 
        ""
    )
)

you might also have helper formulas like:

changed_rate_data_row = LAMBDA(rate_type, period_start, period_end, 
    FILTER(
        rate_change_table, 
        (rate_change_table[rate_type] = rate_type) * 
        (rate_change_table[date] <= period_end) * 
        (rate_change_table[date] >= period_start), 
        FALSE
    )
)

prior_rate_data = LAMBDA(change_date, rate_type, 
    TAKE(
        SORT(
            FILTER(
                rate_change_table, 
                (rate_change_table[date] < change_date) * 
                (rate_change_table[type] = rate_type), 
                FALSE
            )
        ), 
        -1
    )
)

days_in_period_pre_and_post = LAMBDA(change_date, 
    HSTACK(
        DAYS(change_date), 
        EOMONTH(change_date, 0) - change_date + 1
    )
)

And then you start your formula with something like:

=LET(
    rate_change_table, YOUR_TABLE_REFERENCE_HERE,
    target_rate_type, EITHER_TYPE_A_OR_TYPE_B,
    period_start_date, DATE(YEAR(2021), MONTH(1), DAY(1)),
    period_end_date, EOMONTH(period_start_date, 0),

    // Define the rate change row for the target type within the period
    period_change_row, changed_rate_data_row(target_rate_type, period_start_date, period_end_date),
    target_change_date, CHOOSECOLS(period_change_row, 1),

    // Retrieve the prior rate row for the target type before the change date
    prior_rate_row, prior_rate_data(target_change_date, target_rate_type),

    // Calculate the days of prior and post-rate within the period
    days_of_prior_rate_and_post_rate, days_in_period_pre_and_post(target_change_date),

    // Select prior and changed rates
    prior_rate, CHOOSECOLS(prior_rate_row, 3),
    changed_rate, CHOOSECOLS(period_change_row, 3),

    // Continue with your subsequent logic here...
)

At this point, you have your relevant rate data:

• The date of change
• Number of days in the month at that rate (assuming only one change per month)
• The rate at the beginning of the month and after it changed

Next, follow a similar approach to retrieve any additional data conditions you might need, such as whether sales reached a certain percentage of total revenues in the period.

Once you have all your conditions, you can combine them in a structured IFS formula as follows:

IFS(
    AND(period_change_row, sales_to_revenues_percentage > 0.4),
        calculation_for_tier_1(period_change_row, prior_rate_row, days_of_prior_rate_and_post_rate),

    AND(period_change_row, sales_to_revenues_percentage > 0.3),
        calculation_for_tier_2(period_change_row, prior_rate_row, days_of_prior_rate_and_post_rate),

    NOT(period_change_row),
        calculation_for_no_rate_change_in_period,

    TRUE,
        "Invalid data"
)

Explanation of Logic

1. First Condition: If period_change_row exists (indicating a rate change) and sales_to_revenues_percentage exceeds 40%, apply calculation_for_tier_1 with the relevant data.
2. Second Condition: If period_change_row exists and sales_to_revenues_percentage exceeds 30% (but not 40%), apply calculation_for_tier_2.
3. Third Condition: If there is no rate change (using NOT(period_change_row)), apply the calculation for periods with no rate change.
4. Fallback Condition: If none of the conditions are met, return "Invalid data".

This formatting clarifies each logical tier and maintains the readability of your IFS formula, making it easier to troubleshoot and update as needed.

By cleanly separating the following elements:

• Data Retrieval: Collecting all relevant data points needed for calculations
• Flow Paths: Defining the possible calculations or actions to perform
• Conditions: Identifying the specific data conditions that trigger each calculation

you can create a robust structure that:

• Allows for easy modification and reuse
• Ensures future intelligibility for both yourself and others

This approach provides a clear, modular structure where each component—data retrieval, conditions, and calculations—remains separate and straightforward. This independence simplifies both maintenance and adaptability, making each part easy to adjust or expand without disrupting the others.