Vier Prompt-Details liefern bessere Excel-Formeln von ChatGPT

"Give me an Excel formula for this" tends to get a generic answer. The model has to guess which columns hold what, how many rows there are and where the formula will go, so you end up rewriting ranges by hand or going back and forth several times.

TL;DR: Tell it where the data is, what you want calculated, which cell the formula goes in and which Excel you use. Then test the formula on a small table before trusting it.

Column A is name, B is department, C is region, D is sales (rows 2 to 26). Give me one formula for H12 that sums sales where department is Sales and region is Seoul. I'm using Excel for the web.

A correct answer for that prompt looks like this:

=SUMIFS(D2:D26,B2:B26,"Sales",C2:C26,"Seoul") Enter fullscreen mode Exit fullscreen mode The same SUMIFS pattern was checked in Excel for the web on a sample sheet, and the result matched a total calculated in advance.

Test before you use it. Build a tiny table where you already know the answer, run the formula on it, and compare. A formula can be syntactically valid and still sum the wrong column.

Describe the data instead of pasting it. Company spreadsheets often contain names, prices or customer details. The model only needs column names and the shape of the table to write a formula, so there is no reason to paste the sheet itself.

If you already work with pandas, a one-line cross-check is easy:

df. loc [( df [ " Department " ] == " Sales " ) & ( df [ " Region " ] == " Seoul " ), " Sales " ]. sum () Enter fullscreen mode Exit fullscreen mode If the pandas number and the Excel number match, you can be fairly confident the formula's conditions are right. If they don't, the difference usually points to a wrong range or a typo in a condition value.

This article was created with the help of AI. The example formula was tested in Excel for the web.