INDIRECT not working with array formulas - need a new way

[MountainX]

This is my first post here. (And pretty much my first attempt to do anything with OO Calc.) I hope I have my table formatting right in this post.

I'm looking for a way to make smart formulas that I can copy and paste instead of editing the row references in all my formulas by hand. I've been trying ADDRESS, INDIRECT and a number of other approaches without any success.

I want to turn a formula like this one on row 8 (with hard coded "G6" and "F6" references) into something that can figure out how many rows above its location it should begin.

Code: Select all

{=AVERAGE(IF(G6:G8>$G$1,F6:F8, ""))}

Here's my latest attempt. It returns #VALUE!

Code: Select all

{=AVERAGE(IF(indirect(ADDRESS(ROW()-I24+1,7,4,1)&":"&ADDRESS(ROW(),7,4,1))>$G$1,indirect(ADDRESS(ROW()-I24+1,6,4,1)&":"&ADDRESS(ROW(),6,4,1)),""))}

The "I24" reference refers to the highest sequence number in the group this row belongs to.

Here's an example:

Code: Select all

				Criteria	7.5		
				(quality > x)			
							
							
	Date	Sequence	Machine	Data	Quality		
	10:01:00 AM	1	a	2557.4	9.46		
	10:02:00 AM	2	a	2280.99	7.17		
	10:03:00 AM	3	a	990.55	9.77		
	10:04:00 AM	1	b	619.62	8.36		
	10:05:00 AM	2	b	754.52	4.42		
	10:06:00 AM	3	b	724.54	8.86		
	10:07:00 AM	4	b	735.03	4.41		
	10:08:00 AM	1	a	397.8	9.73		
	10:09:00 AM	2	a	605.81	4.11		
	10:10:00 AM	1	b	864.05	7.18		
	10:11:00 AM	2	b	741.88	6.3		
	10:12:00 AM	3	b	736.8	9.35		
	10:13:00 AM	4	b	823.35	8.58		
	10:14:00 AM	5	b	729.87	5.87		
							
							
Begin	End	TestID	Machine	Mean	Std Dev	Attempted	Valid
10:01:00 AM	10:03:00 AM	1	a	1773.975	1107.9302601022	3	2
10:04:00 AM	10:07:00 AM	2	b	672.08	74.1896434821	4	2
10:08:00 AM	10:09:00 AM	3	a	397.8	#DIV/0!	2	1

Basically, I want to write the formulas for the first row in the 2nd table and then be able to copy/paste all the rest.

Commentary on the example:
The criteria is a value I plug in to see live updates in the spreadsheet. It is meant to select only rows that have a quality value above the criteria I plug in.

The first table is just values (no formulas).

The second table groups/summarizes, selects and then statistically describes the data in the first table. One row in this table summarizes/groups N rows from the table above with sequence values starting at 1 and going to N. At the next 1, a new row in the 2nd table is needed.

Here are some example formulas related to the 2nd table (along with my comments about what is lacking):
column: Machine
=E8
=E12
=E14
I need a way, based on the sequence value, to have this pick up the value from the last row.

Formulas for the Mean Column:
{=AVERAGE(IF(G6:G8>$G$1,F6:F8, ""))}
{=AVERAGE(IF(G9:G12>$G$1,F9:F12, ""))}
{=AVERAGE(IF(G13:G14>$G$1,F13:F14, ""))}
Presently I would have to edit the row values in the formulas by hand each for each new row I insert in the 2nd table. There are many thousands of them, so hand editing doesn't work!

column: Attempted
same exact issue as the "Machine" column.

column: Valid
Here's one example formula:
{=COUNT(IF(G6:G8>$G$1,1, ""))}
It has the same problems as the average formula.

[ken johnson]

Perhaps a couple of helper columns with formulas that calculate the first and last rows for each group of data.
With your sample data this array formula Ctrl+filled down until it returns a blank, returns the top row for each group.

Code: Select all

IF(ROW($A1)>COUNTIF($D$6:$D$19;1);""; SMALL(IF($D$6:$D$19=1;ROW($D$6:$D$19);"");ROW($A1)))

This standard formula in the next column, filled down to the last row of the array formula returning a non-blank, returns the Last row...

Code: Select all

=IF(H3="";MAX(ROW($D$6:$D$19));H3-1)

With the helper columns in place, the appropriate averages (I used a value of 8 in G1) are returned by this array formula Ctrl + filled down to the bottom of the second table's Mean column...

Code: Select all

AVERAGE(IF(INDIRECT("G"&H2&":"&"G"&I2)>$G$1;INDIRECT("F"&H2&":"&"F"&I2);""))

See attached doc

MountainX.ods

(10.01 KiB) Downloaded 183 times

Ken Johnson

[ken johnson]

After a second look I see that the sample data results in only 3 rows of the second table, I wrongly assumed that the 4th test was complete.
I have changed the "Last row" helper column formula to

Code: Select all

=IF(H3="";"";H3-1)

and filled it down until it produces a blank.
Formulas in the other table's columns return a blank if the "Last row" formula on that row returns a blank.

MountainX2.ods

(10.54 KiB) Downloaded 201 times

Ken Johnson

[MountainX]

Perhaps a couple of helper columns with formulas that calculate the first and last rows for each group of data.

Very nice thinking. Excellent suggestion. Thank you!

With the helper columns in place, the appropriate averages (I used a value of 8 in G1) are returned by this array formula Ctrl + filled down to the bottom of the second table's Mean column...
Ken Johnson

I looked up " Ctrl + filled down" for Calc and here is what I found:

In Calc, it is possible to copy the source cell exactly, without incrementing, by holding the Ctrl key while clicking the fill handle and dragging.

I assume that's what you mean.

I'm going to play with the two examples you kindly provided and see if I can learn how they work.

I'll reply back with any questions. Thanks again.

[Alex1]

Here's my attempt. The second table is an extension of the first one. The results are in columns I, M, O, P, Q, T, the other columns with formulas can be hidden. Columns O and Q are essentially the same as D and C. By selecting 1 in the dropdown menu in N5 the results are selected. The formulas in row 6 differ from the ones in the next rows, as they do not refer to previous rows. The Sequence column should have a dummy 1 following the last row.

[MountainX]

Ken, you gave me a lot of great ideas in your solution. I'm learning a lot by studying the two example spreadsheets you provided. (I can't thank you enough!)

I think the first version of the "last row helper" is actually closer to what I need.

The data table (first table) will continue to grow with new rows at the bottom. I need to be able to update the summary table by CTRL-filling-down new rows. My first attempt involved altering your "last row helper" to use a really large end range value as so:

Code: Select all

{=IF(H3="",MAX(ROW($D$6:$D$9999)),H3-1)}

But that results in the last valid row of the summary table always showing and "end row" value of 9999. It therefore picks up the wrong value for the "machine" and "attempted" columns, of course. My attempts to resolve that have not been successful so far...

Also, after editing the formulas, what's the right way to get the other columns in my summary table to update? When I made the edit above to the last row helper formulas, the other columns changed to #VAlUE!

[MountainX]

The Sequence column should have a dummy 1 following the last row.

Alex1, thank you. That works for Ken's solution too and it is perfectly acceptable in my actual spreadsheet.

I think I prefer the separate table for the summary results as opposed to extending the data table, but I'm going to experiment with both approaches in my actual data and see which works better. It will probably come down to personal preference.

[MountainX]

I'm using Ken's first solution and it is working excellently. This approach is an order of magnitude better than what I was doing previously.

The only issue I have encountered so far is when I CTRL-fill-down the summary table, if I go even one row further than required by the actual data it practically locks up my computer.

[ken johnson]

I looked up " Ctrl + filled down" for Calc and here is what I found:

In Calc, it is possible to copy the source cell exactly, without incrementing, by holding the Ctrl key while clicking the fill handle and dragging.

I assume that's what you mean.

No, that's not what I meant.
With Calc's array formulas you have to hold down the Ctrl key while dragging the fill handle to copy them into subsequent rows/columns.
If the Ctrl key is not held down, relative references within the array formula do not increment and you end up with a multi-value array formula with all values equal.

I think the first version of the "last row helper" is actually closer to what I need.

The data table (first table) will continue to grow with new rows at the bottom. I need to be able to update the summary table by CTRL-filling-down new rows. My first attempt involved altering your "last row helper" to use a really large end range value as so:

Code: Select all

{=IF(H3="",MAX(ROW($D$6:$D$9999)),H3-1)}

But that results in the last valid row of the summary table always showing and "end row" value of 9999. It therefore picks up the wrong value for the "machine" and "attempted" columns, of course. My attempts to resolve that have not been successful so far...

Perhaps instead of using the 9999 you could use a dynamic named range referring to the Sequence column then use it in the 'First row' and 'Last row' formulas.
You could also set up a warning that alerts you to the fact that there are not enough formula rows in the second table.
In the attached doc I have changed the 'First row' and 'Last row' formulas so that they can be Ctrl+filled down as far as needed (and beyond).
Currently there are not enough formula rows and the warning in I4:K4 merge is showing along with a message in L4:S4 merge, indicating action required.
After a formula row in columns I to R is selected then Ctrl+filled down, to produce enough formula rows, the warning message reverts to a count of 'available formula rows' and the 'action required' message disappears.

MountainX DNR and warning.ods

(13.21 KiB) Downloaded 189 times

Ken Johnson

[MountainX]

Ken, that's very impressive work. Thank you. I think that gives me a total solution.