Advanced formula editing

Hi Kimao2,

I haven't taken a close look at your formula, but this may get you started.

The second table is a duplicate of the first. The checkbox cell is used as a trigger to force a recalculation of the values in column B. (This works in Numbers 2, but Numbers 3 has a more refined recalc behaviour, and won't do a full recalculation on the click of a checkbox that no other cell on the table depends on.)

The numbers in column B are based on the position of each cell in the column, with a random component included to make a drop in value from one row to the next possible.

Here is the formula that compares the last two values in the column. Note that it does require that every non-header cell above the last two also contains a numerical value.

C1: =OFFSET(B$1,COUNT(B),0)-OFFSET(B$1,COUNT(B)-1,0)

A conditional format (conditional highlighting in Numbers 3) rule is set to change the fill colour of C1 to red when the formula result in less than zero.

Regards,

Barry

There are a few problems with that formula.

From what I can tell, LEFT(ADDRESS(MATCH(1E+307;$B$24:$B$258);2);3) will always give you the string $B$ if it finds any number in the column. It is unnecessarily complex when you could replace all that with "$B$".

The MATCH function works differently in Excel and Numbers. It is supposed to find the largest value equal to or less than the number you are searching for. But in Excel, MATCH requires the list to be sorted numerically ascending to work properly. In Excel, when you search for a really large number in a randomly sorted list of numbers (or numbers and non-numerics), what it really does is find last numeric value in the column. In Numbers it returns the largest value equal to or less than the value being searched for, just like it is supposed to do.

You did not mention what your column of data looks like. It makes a difference in the answer. Does it include only numbers (as Barry assumed, though COUNT will also include dates) or is it a mix of numbers and non-numerics? Are there any blank rows (Barry assumed there were not)? Do the numbers appear in ascending or descending order or are they in no particular order?

I do not think there is a one-column formula for locating the last value in a column that may have blank rows interspersed within the data unless the numbers are are in ascending or descending order. I do not know if there is a one-column formula for finding the lowest appearing number in a column of numbers and non-numeric values unless the numbers are in ascending or descending order.

IF, as Badunit suggests, you do NOT have blank cells interspersed in your data, you can do something like this if you want to subtract the last number from the penultimate number.

=INDEX(B,COUNTA(B)−1)-INDEX(B,COUNTA(B))

This does not require sorting.

Also, I generated the example numbers in column B with =RANDBETWEEN(1,5) in cells B1:B7. In Numbers 3.6.1 RANDBETWEEN() recalculates automatically whenever the checkbox in A1 is checked (or any other change is made to the table).

The last three rows in my example are defined as Footer Rows.

SG

Badunit wrote:

"I do not think there is a one-column formula for locating the last value in a column that may have blank rows interspersed within the data unless the numbers are are in ascending or descending order. I do not know if there is a one-column formula for finding the lowest appearing number in a column of numbers and non-numeric values unless the numbers are in ascending or descending order."

My thoughts as well.

This would be possible using an auxiliary column to index the entries, and a lookup function to retrieve the last two entries in the data column.

The blue Warning triangle in cell D1 warns that the formula references/includes cells not containing numeric values. Numbers can be instructed to ignore the warning, or it may be avoided by replacing the null string ( "" ) with zero ( 0 ) in the formula creating the index values in column C. Column C may be hidden, so including strings of zeroes in rows where the data column (B) is empty would not affect the visual display of the table.

Formulas:

C2: =IF(LEN(B)<1,"",ROW())

Alternate:

C2: =IF(LEN(B)<1,0,ROW())

Fill this formula down to the end of column C.

D1: =LOOKUP(LARGE(C,2),C,B)-LOOKUP(LARGE(C,1),C,B)

Regards,

Barry

Hi Kimao2,

Given that the values in column B are numbers, that there are no empty cells above the last two values in that column, and that the table may be laid out to contain only one row above the list of values:

Either of the formulas suggested my post and that of SGIII will do the job quite effectively.

Place the data on a table separate from the one used for summarizing and reporting the results.

Define row 1 of the data table as a Header row.

Assign two conditional Format/conditional highlight rules to the cell containing the Improvement value:

less than 0 set text colour to red

greater than 0 set text colour to green

(a value of exactly 0 will result in text keeping the default colour—probably black)

Regards,

Barry

Assuming your table name is still 'Runs' as in the template, you can do this:

=INDEX(Runs::B,COUNTA(Runs::B))−INDEX(Runs::B,COUNTA(Runs::B)+1)

Change the , to ; if needed for your Region.

Change to table name as needed.

The Conditional Highlighting that worked for for me was this (after first selecting Duration):

SG

What language/region are you using? (It should be straightforward to convert from English U.S. to your language/region.)

SG

Here's what my formula looks like in Norwegian:

=INDEKS(Løp::B;ANTALLA(Løp::B))−INDEKS(Løp::B;ANTALLA(Løp::B)+1)

See help on INDEKS here. See help on ANTALLA here. (I found these links through Help > Formulas and Functions Help in the menu.)

SG