The Excel formula you cannot get, explained and working

Reading time: about 10 minutes

The situation. You have spent twenty minutes fighting a VLOOKUP that returns #N/A, or you want to sum only the rows for one supplier and have no idea how. You know there is a way, just not which one.

A spreadsheet is the closest thing there is to a perfect translator for this: the answer can be checked. Either the formula gives the right number or it does not, and you see it instantly. That makes it the best place to build confidence, and the one that gives you back the most time in week one.

What you need to hand: Your sheet open and the ability to say what is in each column. Nothing else — and certainly not the name of what you are trying to do.

Step by step

Describe your sheet, not your problem

Everyone’s mistake is to start with "my VLOOKUP is not working". That forces the model to guess what your sheet looks like, and it guesses wrong. Start with the sheet: which column is which letter and what kind of data it holds.

Three lines of description are worth more than half an hour of back and forth.

Ask for it naming the program and the language

Excel in one language does not use the same function names as in another, nor as Google Sheets. And the argument separator can be a comma or a semicolon depending on your settings. If you do not say which, you get a correct formula that your sheet rejects — and it looks like the tool failed when what failed was the dialect.

I use Excel in English / Google Sheets. My sheet’s argument
separator is the comma.

My sheet has these columns, header in row 1 and data from row 2 to
row 450:
  A: product code (text)
  B: description (text)
  C: supplier (text, repeats)
  D: units sold last month (number)
  E: cost price (number with decimals)

In cell H2 I want: the total cost value of the units sold, counting
ONLY the rows for the supplier I type into G2.

Give me:
1) the formula ready to paste,
2) one sentence explaining what each part does,
3) what error I would get if I got something wrong, and how it looks.

Do not give me several alternatives: give me the one you would use.

Test it on three made-up rows before your own

Open a new sheet, type three rows by hand whose answer you know in your head, and paste the formula there. If it gives what you worked out mentally for three rows, it will for four hundred.

The other way round — pasting straight into the real sheet — has two problems: you cannot tell whether the result is right because you cannot work it out by hand, and if the formula fills down or overwrites something, you have broken it with no copy.
If it errors, paste the whole error

Do not write "it gives an error". Paste the formula exactly as it ended up and the exact error text and a sample of two rows of your data. With those three things it is usually fixed first try; without them, the guessing game starts.

One warning: if it proposes the same formula twice, do not just insist. Say "we already tried that one and it gives this error" — otherwise it loops.
The separator, which is half the errors

If the formula errors the moment you paste it and the message says little, check this first: the comma versus the semicolon.

Depending on your regional settings, a spreadsheet separates arguments with a semicolon rather than a comma, and most of the examples the model has read use the comma. It is the number one error and has nothing to do with whether the formula is well thought out.

The fix is to say so up front: "I use Excel with the semicolon as the argument separator". And if it still errors, reply "it errors, I use semicolons" and you get it back fixed in a second.

The same goes for function names, which are translated in some localised versions of Excel while Google Sheets keeps the English ones — which is what confuses everybody.
Ask it to explain the formula as if you had to maintain it

This is what separates "it made me a formula" from "I know how to do this now". When it works, ask: "explain what each part does and what I would have to change if I add a new column in the middle tomorrow".

In six months you will open that sheet remembering nothing, and that explanation pasted in a nearby cell is what will save you.

A full example, and what is wrong with it

The brief was: "in an expiry sheet, column F should tell me the days remaining and column G should say EXPIRED, URGENT or CHECK". This is what arrived.

What it replies

In F2: =C2-TODAY()

In G2: =IF(F2<0,"EXPIRED",IF(F2<30,"URGENT",IF(F2<90,"CHECK","OK")))

"Drag both formulas down. The first subtracts today from the expiry date, giving you the days remaining. The second evaluates that number in a cascade: if negative it has expired, if under 30 it is urgent, and so on."

And now, what is wrong with that reply:

The formulas are right and so is the explanation. Worth saying, because this case is not about AI getting it wrong: on formulas it is right nearly always. It is about the three things it did not tell you, which will decide whether the sheet works for you or gets abandoned in a week.
It did not cover the EMPTY cell, and you did not ask either. A half-filled row has C2 empty. Excel treats that as day zero of its calendar, so C2-TODAY() gives a hugely negative number and G2 says EXPIRED. The result: a sheet that screams red for every line somebody started and did not finish — which is why it stops being looked at.
The separator may not be yours. Those use semicolons. If your spreadsheet uses commas, the formula errors the moment you paste it and the message does not tell you why. It is the number one failure here and has nothing to do with whether the formula is well thought out — which is why the separator step exists.
And it did not tell you that you can change the 30 and the 90. They are there because you asked for them, and they are the only part of the formula you will ever want to change. If you do not know that 30 means "thirty days" and is not a magic constant, the sheet stops being yours the moment you want urgent to mean 45. A formula you do not understand breaks the day you need to change it.

Reply "what happens if the date cell is empty?" and it will hand back the version with an emptiness check in front, apologising. The good formula exists and only needs asking for — but you have to know to ask, and that is the whole difference between this case and copying a formula off the internet.

And if your pharmacy is not like that

If you use Google Sheets rather than Excel. Say so in the prompt, because two things change: Sheets accepts English function names whatever the interface language, and it has functions Excel does not. An Excel formula usually works in Sheets; the other way round, not always.

If other people will use the sheet. Ask it to protect the formula columns as well. The first person who types over F2 breaks the whole column and nobody notices until the statuses stop making sense. Ten seconds in Sheets, two clicks in Excel.

If what you have is not a sheet but a pasted list. Before asking for formulas, ask "turn this into columns". A formula over a column where product, batch and date all live in one cell does not exist — and separating first is faster than writing a heroic formula to work around it.

If you are going to use the formula only once. Then do not ask for a formula: ask for the result. Paste the rows and say "give me this column already calculated". A formula is for a sheet that will keep being used; for a one-off calculation over forty rows it is a detour, and it forces you to debug it as well. Formulas start paying off when the sheet gets reopened.

When it does not work first time

I get #VALUE! down the whole column.

The dates are stored as text, not as dates. You can check in a second: if they align right on their own they are dates; if they stay left, they are text. Select the column, set date format, and retype one to confirm it now aligns by itself.

The formula works in row 2 but not when I drag it.

It is nearly always references that should be fixed and are not, or the reverse. Say "when I drag down the reference to X changes and it should not" — with the cell name. And if you want a reference NOT to move, the word to use in the prompt is "absolute".

I ask it to simplify and it makes it worse.

Give it the working formula and say explicitly "do not change it, just explain it step by step". It reads "simplify" as "rewrite", and a rewritten formula is a formula to test again. If it already works, the only thing missing is understanding it.

I want the formula but cannot even describe my sheet.

Paste the first three rows as they are, header row included. From that it works out the columns and their types better than you would describe them, and it saves you the step most people give up at. Just make sure those three rows carry nobody’s data.

Before you use what it gave you

You tested it on three rows whose answer you knew in advance.
It uses the function names of your program and your language.
You know what each part does, at least roughly.
If you fill it down, it still works on the last row (fixed ranges with $ are the classic failure).
You told it which program you use and which argument separator.
If it errored, you pasted the literal message instead of describing it.

It invents functions that do not exist. Especially when mixing programs: it gives you a Sheets function in Excel or the other way round. You see it instantly because the sheet flags it red, so it is one of the good failures: loud.

It will give you the number without the formula if you let it. Paste the data and ask "what does this add up to" and it adds it up itself — and that is generation, not calculation: it can come out differently the second time. What you want is the formula, which your sheet computes, identically every time.

It gives you a formula that works and wrecks performance. Formulas that scan whole columns (A:A) in a four-thousand-row sheet leave the file sluggish. If your sheet goes slow after adding it, say "this sheet has 4,000 rows and is slow, give me a version that does not scan whole columns".

It gives you a function from another version. Newer functions do not exist in older Excel, and the error you get is the generic unknown-name one. If it happens, write "I use an old version, give me an alternative using only IF, VLOOKUP and COUNTIF" — there always is one, longer and working.

Now, with your own. Take the sheet you use most and list the three things you always work out by hand because "it is only two minutes". Ask for all three formulas in one sitting. Those two minutes, multiplied by weeks, are the entire point of this page.

← All practical cases Back to the school →

The Excel formula you cannot get, explained and working

Step by step

Describe your sheet, not your problem

Ask for it naming the program and the language

Test it on three made-up rows before your own

If it errors, paste the whole error

The separator, which is half the errors

Ask it to explain the formula as if you had to maintain it

A full example, and what is wrong with it

And if your pharmacy is not like that

When it does not work first time

Before you use what it gave you