Created attachment 130415 [details] sample spreadsheet including the INDIRECT formula
I created a spreadsheet which contains 6 columns which contain the INDIRECT formula. Cell N5 had a value of "0.692". I then realized that I no longer wanted column G and I deleted it. I then realized that the results for the columns containing INDIRECT were terribly wrong. Cell N5 now had a value of "1.049". It shouldn't have changed. Upon checking, I saw that the formulas for INDIRECT had not been modified to completely take the deletion of column G into account and I had to modify each formula myself. The problem is caused by the INDIRECT formula using an integer to define a column rather than a letter. In the example, cell N5 contains the formula =IF(H5=0," ",INDIRECT((ADDRESS((ROW()-$B5),14,2)))*H5) where the integer "14" defines the 14th column which is equivalent to the letter N. When column G is deleted, the formula, now in cell M5, has been partially modified, "H5" becomes "G5" but "14" remains the same. =IF(G5=0," ",INDIRECT((ADDRESS((ROW()-$B5),14,2)))*G5) "14" should have been changed to "13" as follows: =IF(G5=0," ",INDIRECT((ADDRESS((ROW()-$B5),13,2)))*G5) This might be avoided if instead of using the integer "14" the letter "N" had been used which then would have been changed to "M". (at least I'd hope it would).
compared to excel N4=0.087 excel =IF(H4=0;" ";INDIRECT((ADDRESS((ROW()-$B4);14;2)))*H4) delete I =IF(H4=0;" ";INDIRECT((ADDRESS((ROW()-$B4);14;2)))*H4) delete G =IF(G4=0;" ";INDIRECT((ADDRESS((ROW()-$B4);14;2)))*G4) result 0.180 calc =IF(H4=0," ",INDIRECT((ADDRESS((ROW()-$B4),14,2)))*H4) delete I =IF(H4=0," ",INDIRECT((ADDRESS((ROW()-$B4),14,2)))*H4) delete G =IF(G4=0," ",INDIRECT((ADDRESS((ROW()-$B4),14,2)))*G4) result 0.180
Thank you for your prompt response. I made an error where I said "Cell N5 now had a value of "1.049"." I should have said "Cell M5 now had a value of "1.049" because the contents of column N moved to column M when column G was deleted. Later I did correctly note that the value originally in N5 was now in M5 after column G was deleted when I said "When column G is deleted, the formula, now in cell M5". In your comparison you chose row 4 which has different values but essentially supports my request. I see that you've changed this to an "Enhancement", possibly because both Excel and LO contain the same incorrect logic. My premise is that if the "H5" is changed to "G5" then so should "14" be changed to "13" and, since it doesn't change, this is a bug/error, it's not an enhancement. Microsoft should also change Excel, should they not? Possibly LO is this way because the current code copies what MS does in Excel "for compatability" i.e. copy the errors. At least I've identified the error even though it may never be fixed because it would then be incompatable with Excel.
and it's obvious through your post that you have "Confirmed" this, why is it still showing as "Unconfirmed"?
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)
(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.