How Excel Formulas Get Converted to JavaScript for Web Apps

We've already covered how VBA macros get converted to JavaScript. But most spreadsheets don't use VBA at all. They rely on standard Excel formulas: VLOOKUP, SUMIFS, nested IFs, and dozens of other functions that handle the heavy lifting. When we convert a spreadsheet into a web application, every one of those formulas needs a JavaScript equivalent that produces the exact same result.

Here's how the most common Excel functions translate to JavaScript, with real code examples.

Lookup Functions: VLOOKUP, INDEX-MATCH, and XLOOKUP

Lookup functions are the most common formulas we encounter. In Excel, VLOOKUP searches a column for a value and returns a corresponding value from another column. It's the backbone of pricing tables, tax brackets, and reference data.

Excel:

=VLOOKUP(B5, PricingTable, 3, FALSE)

JavaScript equivalent:

const pricingTable = [
  { sku: "A100", name: "Basic Widget", price: 29.99 },
  { sku: "A200", name: "Pro Widget", price: 59.99 },
  { sku: "A300", name: "Enterprise Widget", price: 149.99 }
];

function vlookup(searchValue, table, key, returnKey) {
  const row = table.find(item => item[key] === searchValue);
  return row ? row[returnKey] : null;
}

// Usage: vlookup("A200", pricingTable, "sku", "price") returns 59.99

The JavaScript version is actually more readable than the Excel original. Instead of referencing column numbers (what is column 3?), we use named keys ("price"). And instead of searching cell ranges, we search an array of objects.

For INDEX-MATCH and XLOOKUP, the pattern is similar. The key difference is that cell ranges become arrays, and the lookup logic uses JavaScript array methods like .find(), .findIndex(), or Map objects for faster lookups on large datasets.

Conditional Aggregation: SUMIFS, COUNTIFS, AVERAGEIFS

These functions filter data by one or more criteria and then aggregate the matching values. They're everywhere in financial models, sales reports, and inventory systems.

Excel:

=SUMIFS(Revenue, Region, "Northeast", Year, 2026)

JavaScript equivalent:

const salesData = [
  { region: "Northeast", year: 2026, revenue: 45000 },
  { region: "Southeast", year: 2026, revenue: 38000 },
  { region: "Northeast", year: 2026, revenue: 52000 },
  { region: "Northeast", year: 2025, revenue: 41000 }
];

function sumifs(data, sumField, ...conditions) {
  return data
    .filter(row => {
      for (let i = 0; i < conditions.length; i += 2) {
        if (row[conditions[i]] !== conditions[i + 1]) return false;
      }
      return true;
    })
    .reduce((sum, row) => sum + row[sumField], 0);
}

// Usage: sumifs(salesData, "revenue", "region", "Northeast", "year", 2026)
// Returns 97000

The .filter().reduce() pattern is the JavaScript equivalent of conditional aggregation. Filter the rows that match your criteria, then reduce (sum, count, or average) the remaining values. COUNTIFS becomes .filter().length. AVERAGEIFS becomes .filter().reduce() divided by the filtered count.

Nested IF Statements

The classic deeply nested IF is one of the most common patterns in business spreadsheets. We regularly see 5 to 8 levels of nesting, and we once encountered a formula with 14 nested IFs. In Excel, it looks like this:

Excel:

=IF(score>=90,"A",IF(score>=80,"B",IF(score>=70,"C",IF(score>=60,"D","F"))))

JavaScript equivalents:

// Option 1: Lookup object (best for exact matches)
const grades = { 90: "A", 80: "B", 70: "C", 60: "D" };

function getGrade(score) {
  const thresholds = [90, 80, 70, 60];
  for (const threshold of thresholds) {
    if (score >= threshold) return grades[threshold];
  }
  return "F";
}

// Option 2: Early returns (best for complex conditions)
function getGrade(score) {
  if (score >= 90) return "A";
  if (score >= 80) return "B";
  if (score >= 70) return "C";
  if (score >= 60) return "D";
  return "F";
}

Both approaches are dramatically more readable than the nested IF formula. The early-return pattern is our default choice because it's easy to understand, easy to modify, and handles any number of conditions without nesting.

Rounding Functions: ROUND, ROUNDUP, ROUNDDOWN

Financial spreadsheets live and die by rounding precision. Excel's ROUND, ROUNDUP, and ROUNDDOWN have direct JavaScript equivalents, but there's a critical gotcha.

Excel:

=ROUND(price * taxRate, 2)
=ROUNDUP(total / 12, 2)
=ROUNDDOWN(discount, 0)

JavaScript equivalents:

// ROUND to N decimal places
function round(value, decimals) {
  const factor = Math.pow(10, decimals);
  return Math.round(value * factor) / factor;
}

// ROUNDUP (always rounds away from zero)
function roundUp(value, decimals) {
  const factor = Math.pow(10, decimals);
  return Math.ceil(value * factor) / factor;
}

// ROUNDDOWN (always rounds toward zero)
function roundDown(value, decimals) {
  const factor = Math.pow(10, decimals);
  return Math.floor(value * factor) / factor;
}

// Usage: round(19.995, 2) returns 20.00
// Usage: roundUp(19.991, 2) returns 20.00
// Usage: roundDown(19.999, 0) returns 19

The gotcha: JavaScript's floating-point arithmetic can produce results like 0.1 + 0.2 = 0.30000000000000004. For financial calculations, this matters. We handle this by rounding at each calculation step and, for currency values, sometimes working in cents (integers) rather than dollars (floats). When a client's spreadsheet involves money, we test rounding behavior extensively to ensure the web app matches Excel to the penny.

Date Calculations

Date math is consistently the trickiest part of spreadsheet conversions. Excel stores dates as serial numbers (days since January 1, 1900), which makes arithmetic simple but creates some interesting challenges in JavaScript.

Excel:

=DATEDIF(startDate, endDate, "M")     // Months between dates
=EDATE(startDate, 6)                   // Date 6 months from start
=NETWORKDAYS(startDate, endDate)        // Working days between dates

JavaScript equivalents:

// Months between two dates (like DATEDIF with "M")
function monthsBetween(start, end) {
  const startDate = new Date(start);
  const endDate = new Date(end);
  return (endDate.getFullYear() - startDate.getFullYear()) * 12
    + (endDate.getMonth() - startDate.getMonth());
}

// Add months to a date (like EDATE)
function addMonths(date, months) {
  const result = new Date(date);
  result.setMonth(result.getMonth() + months);
  return result;
}

// Working days between dates (like NETWORKDAYS)
function networkDays(start, end) {
  let count = 0;
  const current = new Date(start);
  const endDate = new Date(end);
  while (current <= endDate) {
    const day = current.getDay();
    if (day !== 0 && day !== 6) count++;
    current.setDate(current.getDate() + 1);
  }
  return count;
}

NETWORKDAYS is a great example of a function that looks simple in Excel but requires careful implementation in JavaScript. The Excel version also supports an optional holidays parameter, which we implement by passing an array of holiday dates and checking against them in the loop. For projects with heavy date logic, we sometimes use a lightweight date library to handle timezone and locale edge cases.

Cross-Sheet References and Named Ranges

In Excel, a formula on Sheet1 can reference data on Sheet2 with syntax like Sheet2!B5 or PricingData!rates. The web app equivalent depends on the application architecture:

For simple calculators: All data lives in a single JavaScript module. Cross-sheet references become references to different objects or arrays within that module.

// What was Sheet2!B5 becomes:
const config = {
  taxRate: 0.0825,       // Was Sheet2!B5
  discountThreshold: 1000, // Was Sheet2!B6
  maxQuantity: 500        // Was Sheet2!B7
};

// What was =Sheet1!A1 * Sheet2!B5 becomes:
const total = inputValue * config.taxRate;

For larger applications: Each sheet becomes its own module or component. Named ranges become well-named constants or configuration objects. This modular structure makes the code easier to maintain than the original spreadsheet, where dependencies between sheets can be hard to trace.

Named ranges in Excel translate directly to named constants in JavaScript. annual_revenue in Excel becomes const annualRevenue in JavaScript. The intent is preserved, and the code is self-documenting.

How We Verify Accuracy

Translating formulas is only half the job. The other half is proving that the translation is correct. Our verification process is rigorous:

1. We build a test matrix. For each formula or calculation chain, we create 50+ input combinations covering normal values, edge cases (zeros, negatives, very large numbers), and boundary conditions (values right at threshold cutoffs).
2. We run inputs through both systems. The same 50+ inputs go through the original Excel spreadsheet and the new JavaScript web app.
3. We compare outputs to the penny. Every result must match exactly. For financial calculations, we compare to two decimal places. For scientific calculations, we match to the precision specified in the original spreadsheet.
4. We document any intentional differences. Sometimes the web app improves on the spreadsheet, like handling edge cases that Excel produces #DIV/0! or #N/A errors for. These are documented and approved by the client.

If you prepare test cases before sending your spreadsheet, those become the first entries in our test matrix. The more test cases you provide, the faster and more confident the verification process.

Have a formula-heavy spreadsheet that needs to become a web application? Learn more about the conversion process or send us your file for a free quote.