Yesterday night I held a training in California – per videoconference, in order to minimize travel costs.

„Katharina, my formula works correctly, but some lines later all of a sudden an error occurs“

It was not necessary to see the participants‘ screen to solve the riddle: „I assume that you forgot to fix some cell reference with $-signs?“ – „Bingo!“

No, it’s not magic :-). But one of the most common faults while setting up a formula is hastily forgetting to think about which cell reference should „slide“ when dragging the formula to other cells, and which cell reference is the same for each and every goal.

See: we multiply the number of Pics (B2) with the price of a single picture (A1). And because the price of a single picture is the same for all the upcoming rows, we fix it by adding $-signs preceding the row and the column =B2*$A$1

So if we drag the Formular downwards in order to fill in the rest of the Costs fields, B2 evolves into B3, B4, …, but $A$1 always refers to A1. See:

If I had forgotten to add the $, the first formula would have had the same result. But then there would have been errors. Why? Because you can’t multiply B7 with Kloiber, and so can’t Excel.

This happens that often – you created a wonderful formula, and it works quite well, but when you drag it to other cells there are lots of errors – a sure sign of a missing $.

Interested how a training per videoconference works? I’ll blog about the setup some days later – now I’m too tired. Had been a late night yesterday here in Austria 😉