Can i make an exception to a formula

An "invalid reference" error usually means the formula is referencing a cell that does not exist, or (possibly) is referencing a cell containing a formula that references a cell tha does not exist.

Here'a a table resemblng what you've described, but with a header row (row 1) containing labels for the data in each column, and the actual data entries and calculation beginning in row 2.

The table contains two formulas, based on the two in your initial post, and the revisions shown in Badunit's post above. These are shown below the table image.

The first formula: IF(B2="","",A2-B2)

is entered as shown in C2, then filled down to the last row of column C.

The second formula: IF(B2="","",C2)

is entered in A3, then filled down to the end of column A.

In each, the core part of the formula is shown above in bold type. The core is wrapped in an IF statement that says "If cell B2 is empty or contains a null string ( "" ), place a null string in this cell. Otherwise, do the "if false" action (the part shown in bold).

Results after an entry in B2 are shown above, Results after two more payments have been entered (in B3 and B4) can be seen below.

Regards,

Barry

Assuming you want the beginning balance to show in the next row, but not the ending balance,

Cell A1 is data you enter by hand so the first formula in column A will be in A2

Cell A2 =IF(B1<>"",C1,"")

fill down to complete the column

Cell C1 =IF(B1<>"",A1-B1,"")

Fill down to complete the column

The Help menu in Numbers is a good place to start.

This is from an older version than the one you are using, but the menu items may be the same. The highlighted menu item is an excellent introduction to formulas and the functions that can be used in their construction. The link, in my installed version of Numbers, is to a document that lists and discusses only the functions as of 2019. Yours may go to an updated document.

A second excellent resource is the Functions Browser, which opens whenever you select a cell and type =.

The browser lists all functions supported in Numbers, gives a description of the function, the syntax of a formula using the function, and at least one example of a formula constructed with that function, and an examply of where it might be used in a spreadsheet.

The core formulas used in my examples above are pretty basic ones. The only 'esoteric' part is the switch that keeps cells 'blank' until there is a result to calculate.

Badunit's version and mine do the same job—preventing the calculation until there is data entered in a cell needed to complete the calculation.

**:  Cell C1 =IF(B1<>"",A1-B1,"")     'if B1 is not 'empty', do this, otherwise, place a null string.'

Barry:            IF(B2="","",A2-B2)       'if B2 is 'empty', place a null string, otherwise, do this.'

I prefer 'my' version only because it lets me wrap the core formula with only the closing parenthesis following that core part.

Regards,

Barry

If the error is a "syntax error" and you use a comma for a decimal separator where you are located (i.e., 1,00 not 1.00), the parameters in formulas are separated by semicolons not commas.

Cell A2 =IF(B1<>"";C1;"")

Cell C1 =IF(B1<>"";A1-B1;"")

"I didn't know which symbols represent the 'not empty' and 'empty'."

A cell containing a null string (a text value with zero length) isn't really 'empty,' but IF will accept that value as the equivalent of 'empty.'

Any content that is placed between opening and closing double quotes in a formula is treated as text. A null string (also known as 'the empty string' ) is a special case. entered as a pair of double quotes with nothing between them. It's and easy thing to misread in the text here, but somewhat easier to spot and recognize in the formula editor images in my initial reply.

Regards,

Barry