Replace Nested IF Formulas with Classification Rules

When to use this skill

• Your workbook has a formula like =IF(A2=11,"Fund_A",IF(A2=12,"Fund_B",IF(A2=13,... and nobody dares touch it.
• Adding one new fund or department code means editing the same fragile formula in a dozen workbooks — and Excel caps you at 64 nested IFs anyway.
• You need classification rules that combine conditions (account range AND fund code AND amount threshold), which nested IFs make unreadable.

Two clean replacements: a dictionary lookup when it’s a straight code-to-name mapping (this is a VLOOKUP table living in code, or loaded from a small Excel file), and np.select when rules involve ranges and multiple conditions. Both send anything unrecognized to an explicit “Unknown” bucket instead of a silent blank.

Inputs it expects

• Data file: transactions with the driver columns for classification (e.g., Fund_Code, Account_num, Amount).
• The business rules: the nested IF translated into plain English — get the person who owns the formula to confirm each branch, including what happens when nothing matches.
• Optionally, a mapping file (fund_mappings.xlsx with Fund_Code, Fund_Name) so non-programmers can maintain the code list without touching Python.

Steps

1. Write out the nested IF branch by branch as a plain list of rules. This is the moment hidden gaps get found — do it with the formula’s owner.
2. For simple code-to-name mappings: build a dictionary (or load it from Excel with dict(zip(...))) and apply it with .map(), defaulting misses to 'Unknown'.
3. For multi-condition rules: express each rule as a boolean condition and use np.select(conditions, choices, default='Unknown'). Order matters — the first matching condition wins, just like nested IFs.
4. Record the input row count and total amount.
5. Classify, then count how many rows fell into each category, including Unknown.
6. Spot-check against the old spreadsheet: run both on the same data once and diff the category columns before retiring the formula.
7. Export the Unknown rows as the exception list.

Code

import pandas as pd
import numpy as np

df = pd.read_excel('gl_detail.xlsx')   # Account_num, Fund_Code, Amount
rows_in = len(df)
amount_in = df['Amount'].sum()

# --- Simple mapping: replaces =IF(A2=11,"Fund_A",IF(A2=12,... ---
# Maintainable in Excel: fund_mappings.xlsx with Fund_Code / Fund_Name columns
fund_map = pd.read_excel('fund_mappings.xlsx')
fund_dict = dict(zip(fund_map['Fund_Code'], fund_map['Fund_Name']))

df['Fund_Name'] = df['Fund_Code'].map(fund_dict).fillna('Unknown')

# --- Multi-condition rules: replaces the unreadable mega-IF ---
acct = df['Account_num'].astype(str).str[:4].astype(int)   # first 4 digits drive the rules

conditions = [
    (acct.between(1000, 1999)) & (df['Amount'] > 10_000),   # first match wins, like nested IFs
    (acct.between(1000, 1999)),
    (acct.between(2000, 2999)) & (df['Fund_Code'].isin([11, 12, 13])),
    (acct.between(2000, 2999)),
    (acct.between(3000, 3999)),
    (acct.between(4000, 4999)),
]
choices = [
    'Major Revenue',
    'Minor Revenue',
    'Operating Expense',
    'Capital Expense',
    'Asset',
    'Liability',
]
df['Category'] = np.select(conditions, choices, default='Unknown')

# Control totals and exceptions
summary = df.groupby('Category')['Amount'].agg(Rows='count', Total='sum')
print(summary)
print(f"\nRows in: {rows_in:,} | Rows classified: {summary['Rows'].sum():,}")
print(f"Amount in: {amount_in:,.2f} | Amount out: {summary['Total'].sum():,.2f}")

df[(df['Category'] == 'Unknown') | (df['Fund_Name'] == 'Unknown')] \
    .to_excel('EXCEPTIONS_unclassified.xlsx', index=False)
df.to_excel('gl_detail_classified.xlsx', index=False)

Validation (control totals)

• Rows in vs. rows out: classification adds columns, never rows — summary['Rows'].sum() == rows_in, always.
• Classified + Unknown = total: every row lands in exactly one bucket. Report rows per category including the Unknown count; a sudden jump in Unknown means new codes appeared upstream.
• Amount tie-out: summary['Total'].sum() == amount_in to the penny — the category subtotals plus the Unknown bucket must rebuild the original grand total.
• Parallel run: for one period, compare Python categories to the legacy formula’s output row by row (df['Category'] != df['Excel_Category']). Zero differences (or explained ones) before the formula is retired.

Exceptions to surface

• Every row in EXCEPTIONS_unclassified.xlsx — codes or account ranges no rule covers. These are new funds/accounts needing a rule, or data-entry errors. Never let them ride as blanks.
• Rows where the parallel run disagrees with the old Excel formula — each difference is either a bug in the new rules or a bug that’s been living in the spreadsheet for years. Both are worth knowing.
• Rows that matched an earlier condition when a later, more specific one also applied — if two rules overlap, confirm the ordering reflects the intended business priority.
• Fund codes present in fund_mappings.xlsx but never seen in the data (stale mappings), and any duplicate Fund_Code entries in the mapping file — the dictionary silently keeps the last one, so a reviewer should resolve duplicates in the source file.