What does C18:C377-G18:G377 mean?

  • Thread starter Thread starter mattiasw
  • Start date Start date
M

mattiasw

There is an old loan mortgage calculator workbook from Microsoft that
has formulas like

=IF(AND(Pay_Num<>"",Sched_Pay+Extra_Pay<Beg_Bal),Beg_Bal-Princ,IF
(Pay_Num<>"",0,""))

where Pay_Num is a range. It is not an array formula, i.e. not { }
around

If you replace the names with ranges, you get

=IF(AND(A18:A377<>"",D18:D377+E18:E377<C18:C377),C18:C377-G18:G377,IF
(A18:A377<>"",0,""))

which still works. The formula on row 18 has the same meaning as

=IF(AND(A18<>"",D18+E18<C18),C18-G18,IF(A18<>"",0,""))

and the one or row 19 means

=IF(AND(A19<>"",D19+E19<C19),C19-G19,IF(A19<>"",0,""))

But actually, what is the general rule behind this? Having a rule
like

"If you want a single cell for your operation, but the formula
contains a range, take the value on your row".

How can this be generalized to handle horizontal ranges.

Do anyone have any good pointers on what the definition of "C18:C377-
G18:G377" is.

-- mattias
 
If I put 1,2,3,...10 in A1:A10 and =A1:A10 in B1, I will see 1 (from A1)
since Excel takes only the first reference
When the formula is copied down the column, in B2 it becomes =A2:A11 so I
see 2 (from A2)
etc
Hope this helps
best wishes
 
If I put 1,2,3,...10 in A1:A10 and =A1:A10 in B1, I will see 1 (from A1)
since Excel takes only the first reference
When the formula is copied down the column, in B2 it becomes =A2:A11 soI
see 2 (from A2)
Click to expand...

Yes, always using the first cell in the range would be a simple
explanation, but Excel doesn't work in that way

Since it is the EXACTLY the same formula standing on row 18 and 19, on
both rows it says

=IF(AND(A18:A377<>"",D18:D377+E18:E377<C18:C377),C18:C377-G18:G377,IF
(A18:A377<>"",0,""))

However, on row 18, A18:A377<>"" means A18<>""

and in row 19, A18:A377<>"" means A19<>""

So exactly the same formula means different things depending on the
row it is placed upon.
 
Back
Top