Please explain a formula

[Miss Mattax]

I'm working on a worksheet that already has formulas listed and I don't
understand one of the formulas. FYI...all the formulas I'm listing equals a
total number of units.

The formula in row 2 is written CZ1:CZ2-AS2.
The formula in row 3 is written CZ2:CZ3-AS3, etc.

CZ 1 is blank because it's a part of the header, CZ2 = 320, CZ3 = 267, AS2
= 320, and AS3 = 267

I get that the author wants to compare that the original total of units (AS2
or AS3) matches what is in CZ2 or CZ3. The end result should be zero if the
units match.

What I don't get is why is one row being compared to a previous row.

Have any of you ever seen a formula like this? And it doesn't follow the
mathematical rules for me either. I'm assuming it's taking the sum of CZ1 &
CZ2 then subtracting AS2 from the total, but I don't understand how the end
result is always zero.

Please help. It's quite possible there's an error with the formula, but I
wanted to know if you all have ever seen a formula like the one metioned
above.

Thanks,

 

[Sheeloo]

Formulas have a "=" or "=" before them...

Pl. let us know the complete formula in CZ1, CZ2. I am assuming that you see
the result as 0...

Let us know.

 

[Miss Mattax]

Sheeloo; there is an equal sign before all of them.

The formula in row 2 is written =CZ1:CZ2-AS2.
The formula in row 3 is written =CZ2:CZ3-AS3, etc.

 

[Sheeloo]

If you move to the cell with the formula (without getting into the Edit mode)
you will probably see the formula as
{=CZ1:CZ2-AS2}
{} indicate that the formula is an ARRAY formula.

If not then go to the first cell with the formula, edit it and press
CTRL-ENTER-SHIFT

It is essentially adding the current row and previous row values in CZ and
substracting the current row value in AS.

If you treate CZ as Credit column, AS as Debit column with opening balance
in CZ1, the formula will give you the balance...

Pl. mark this as answered if this is what it is.

 

[Miss Mattax]

Sheeloo,

Why are we trying to make the formula an array? When I select the cell all
I see is "=CZ1:CZ2-AS2".

Short of contacting the creator of the spreadsheet I was trying to figure
out why that is done. Something is obviously wrong because it shouldn't
equal zero to me, with the exception of the first forumla.

Essentially I'd get 0+320-320=0 and 320+267-267=320, but the ending result
says 0.

I guess the formula is wrong, because CZ1 is a part of the header field.
The column header says # Units. It's a wonder the end result didn't return
an invalid error. I don't know the ins and outs of Excel, but something is
going on with the formula.

BTW...I tried adding the brackets to the first line and pressing
CTRL-Enter-Shift and the field no longer calculates....it only showed what
was typed in the cell.

 

[Sheeloo]

We are not trying to make it an array. It IS an ARRAY formula since CZ1:CZ2
is an array.
We are just trying to understand the formula.

You do not have to add {} by hand.

Type
=CZ1:CZ2-AS2
in the cell then press CTRL-SHIFT-ENTER and Excel will add {}

You will get an error in row2 after the above...

So do the above with row 3... and copy down...

And you are right... you can simply use
=CZ1+CZ2-AS2 instead of the ARRAY formula.

Array formula makes sense if you had
=$C$Z1:CZ2-AS2
which will increase the range everytime you copy it down...
but then you can use
=SUM($C$Z1:CZ2)-AS2

I guess the previous user preferred his method.

 

[Miss Mattax]

Sheeloo, thanks for your help.

I just think the formula is weird and I'm going to change it so that it's
not adding a total from the previous line to the current line and then
subtracting from the current line.

I'm going to post another question. Maybe you can assist with that one.
It's using if statements.

Thanks again.