locate first and last value in row

you you post a representative example or the data?

OK... so.... which row is the "time sequence row"?

HI ea,

Here's an example using the sample data you supplied. I ignored the last two rows of your table. Including them is a simple matter of filing the formulas down another two rows in the Index and Report tables.

The Index table is a copy of the Main table, with the formula below inserted in cell B2, then filled down to B21 and right to column M.

Index::B2: =IF(SUM(Main :: $A2:B2)>SUM(Main::$A2:A2),COLUMN()-1,"")

The formula creates the index values shown on this table.

The Report table uses these index values and the OFFSET function to retrieve the first and last non-zero values from each row (in columns B and C respectively), then, in column D, subtracts the first value from the last. All are entered into Row 2, then filled down to row 21.

Report::B2: =OFFSET(Main :: $A2,0,MIN(Index :: 2:2),1,1)

Report::C2: =OFFSET(Main :: $A2,0,MAX(Index :: 2:2),1,1)

Report::D2: =C-B

To use an added column on Main to show these results, the three report formulas can be combined and placed in cells Main::N2-N21. As above, enter the formula into N2, then fill down to N21. (The Report table is not used in this version.)

The Index table may be moved to a separate sheet, placed behind the Main table, or otherwise hidden.

Regards,

Barry

Hi,

The third last row of your table has only one cell whose value of entries (1,316) is not zero. In this case how do you calculate the difference between the first and the last entries? I think AppleScript would be useful for these kinds of calculation.

T2

Hi T2YUKI,

If there is a single entry, than that entry is both the first entry and the last entry, and the difference between them is zero.

Regards,

Barry

Thank you, Barry.

Here's an AppleScript solution for Numbers 2.3. (The script would be simpler for Numbers 3. Don't have Numbers 2 and 3 open at same time.).

1. Copy-paste the script into Script Editor (in your Applications > Utilities folder)
2. Change properties (if needed) to match your table.
3. Click once in a cell in your table.
4. Click the triangle 'run' button.

The results (the script places them in column N but this is easily changed) for your posted data are:

SG

property startCol : 2 -- column "B" - change to match your table

property endCol : 13 -- column "M" - change to match your table

property tgtColumn : 14 -- column "N" - change to match your table

property headerRows : 1 -- change to match your table

property footerRows : 0 -- change to match your table

tell application "Numbers"

tell document 1

tell (sheet 1 whose tables's selection range's class contains range)

tell (table 1 whose selection range's class is range)

repeat with r from (headerRows + 1) to (row count - footerRows)

set vv to row r's cells startCol thru endCol's value

set {firstNZ, lastNZ} to {0, 0}

repeat with i from 1 to count vv

tell vv's item i

if it is not 0 then

if firstNZ is 0 then set firstNZ to it

set lastNZ to it

end if

end tell

end repeat

set rowr'scelltgtColumn'svalue to lastNZ - firstNZ

end repeat

end tell

end tell

end tell

end tell

Hi SG,

I like that loop routine at the core of this script.

repeat with i from 1 to count vv

tell vv's item i

if it is not 0 then

if firstNZ is 0 then set firstNZ to it

set lastNZ to it

end if

end tell

end repeat

Very clever!

Regards,

Barry

Hope you give the script a try. One click gives you the same result as setting up an intermediate table (not a sheet) with various formulas.

Numbers is scriptable for a reason. Without array functions, solving problems like this with a script that can loop is much easier and cleaner than going through contortions with non-array formulas.

SG

Hi agin,

This is my version of AppleScript for Numbers '09. I hope you like it.

property dN : "DocumentName" -- change to match your document name

property sN : "SheetName" -- change to match your sheet name

property tN : "TableName" -- change to match your table name

property sC : 2 -- column "Jan" - change to match your table

property eC : 13 -- column "Dec" - change to match your table

property tC : 14 -- column "Difference between the first and last values" - change to match your table

property hR : 1 -- the number of header rows - change to match your table

property fR : 0 -- the number of footer rows - change to match your table

tell application "Numbers"

tell documentdN'ssheetsN'stabletN

repeat with i from (hR + 1) to (row count - fR)

set vList1 to value of cells sC thru eC of row i

set {firstValue, lastValue} to my removeZeroItems(vList1)

set value of cell tC of row i to (lastValue - firstValue)

end repeat

end tell

end tell

-- A handler to return a list of the first- and last-nonzero items in a received list

on removeZeroItems(aList)

set newList to {}

repeat with theItem in aList

if theItem as integer is not 0 then set newList to newList & theItem

end repeat

return {the first item, the last item} of newList

end removeZeroItems