https://bugs.documentfoundation.org/show_bug.cgi?id=105323
--- Comment #6 from Aprax <[email protected]> ---
(In reply to GerardF from comment #5)
> When adding or deleting row(s), references are updated consequently.
> But only cell references. Why should we change an integer ?
> Imagine that in a cell I have a formula =$A$1*10
> deleting a row between 2 and 10 should not change the formula in =$A$1*9.
>
> If you want the formula to change, don't use integer but a function.
> In this case, replace the number 14 by COLUMNS($A$1:$N$1)
First, it involves deleting a column not a row.
Second, the integer is part of the function ADDRESS that points to a column
identified by an integer. when "x" columns to the left are deleted, the
function needs to point to "x" columns to the left, not the original column.
If the function allowed the use of letters for the column and if the letter was
changed (in this example)from N to M, the function would work properly. And it
should even when using integers.
so instead of specifying the 14th column (which is N)
=IF(G5=0," ",INDIRECT((ADDRESS((ROW()-$B5),14,2)))*H5)
use the column letter $N
=IF(G5=0," ",INDIRECT((ADDRESS((ROW()-$B5),$N,2)))*H5)
and when column G is deleted, change $N to $M and H5 to G5
=IF(G5=0," ",INDIRECT((ADDRESS((ROW()-$B5),$M,2)))*G5)
I hope that's clear enough for you to understand.
Actually the function with the 'error' is ADDRESS. It's just that INDIRECT
requires the use of ADDRESS.
I'm sure that someone copied both formulas from Excel without any testing.
LO could, when reading in an Excel file, translate the integer (14) to a letter
($N), act properly and then when writing out the sheet to an Excel format,
translate the letter ($M) back to an integer (13). That would be one way the MS
Excel users could fix their sheets, use the competitions (LO-Calc) correct
functions.
ADDRESS requires you to specify that you want to look for a "ROW()" that is
offset by "-$B5" or -2 rows (negaative means it's above) from the row in which
ADDRESS resides, and if it finds that row (row 3 in this example) without
error, to return the value "0.087" which it finds in the 14th column of that
row (which would be N3).
INDIRECT uses ADDRESS to find the value ("0.087") which it then multiplies by
the value in H5 ("7.9500"), in this example that's "0.087" * "7.9500"
resulting in a value "0.692" in cell N5.
Because of the error/bug, after deleting column G, the result, now in M5, is
the "kWh low", now in G5 ("7.9500", which is correct) multiplied by the price
for "mid" ("0.132"), now in N3) and that's wrong. It should be using the price
for "low" now in M3 (0.870").
The purpose of this sheet is to record the daily usage of electricity, to price
it and calculate the total $ which I should be billed, it acts as a "proof"
that I'm billed correctly.
Column A is the dates within a billing period
column B is the number of that date from the beginning, or Day 1, Day 2, ....
Day 35.
Columns H, I & J contain the usage in kWh which are priced from low to high
Columns N, O & P contain the rates & costs for basic electricity usage, from
low to high
Columns S, T & U contain rates & costs that include electricity + other
charges, priced from low to high.
The rates can change at the beginning of a billing period (month) and do on
both regular and irregular schedules. That's why there's a pricing row before
each set of rows for a billing period. In my real life sheet I decided to
re-purpose column G rather than delete it because I'd have to correct too many
formulas using the INDIRECT/ADDRESS functions.
So, I can live with what I have now, but the error should be addressed (no pun
intended) and fixed for future use, not just by me but everyone.
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
[email protected]
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
