Previous 1 2 Next 23 Replies Latest reply: Jan 5, 2013 10:39 AM by Barry Go to original post
  • Badunit Level 6 Level 6 (11,380 points)

    Trexo,

     

    Wayne's formula gives you the value in the last row of the table.  It sounds to me like you want the last value in the column, not the value in the last row of the table.  This is why you are getting a result of "0", you have nothing entered in the last row.  You need Jerrry's solution.  And, yes, it works on the iPad.

     

    Turning off what you are calling "autocomplete" would make formulas harder to construct on the iPad. It is great the way it is.

     

    That Excel formula is a mess.  It uses a design flaw in the MATCH function in Excel to do its work. MATCH is supposed to be finding the largest value less than or equal to 9.999E307. In other words it should find the largest value in the column, not the last value in the column. The formula works as it is supposed to in Numbers.

  • trexo Level 1 Level 1 (0 points)

    Jerry,

     

    I was able to recreate your first chart and it worked as you stated.  And it is exactly what I am looking for.  However, when I did the same formula in my imported excel file it did not work.  When I insert the formula in the footer of row D, that is the row where my data is, I get an error which says:  formula cannot reference its own cell or depend on another formula that references this cell.

     

    When I move the formula to the footer in column c it returns nothing.  The formula is accepted but it does not return a value.  It is just blank.

     

    Is there something in the conversion of excel files that hinders some formulas from working?  I have too much data to manually recreate the file.

     

    Thank you again for your patience.

     

    Jim

  • trexo Level 1 Level 1 (0 points)

    I have 10 separate sheets with all data starting in cell D7.  I amended your formula to be =offset(D7,Count(D),0).  When I placed your formula in B4 the error came back: This formula contains an invalid refernce.

  • trexo Level 1 Level 1 (0 points)

    I have gone back and re-entered your formula in the footer of 4 of the sheets.  In all 3 of them a 0 is returned.  In one of them the value 10 up from the bottom of the column is returned. 

     

    I hope as I give you more information something clicks for one of us!

  • Barry Level 7 Level 7 (29,205 points)

    " When I insert the formula in the footer of row D, that is the row where my data is, I get an error which says:  formula cannot reference its own cell or depend on another formula that references this cell."

     

    I'm assuming you mean "the footer row of column D".

     

    The error message means that the formula's range, D:D, includes the last (regular) cell in column D. That would indicat that the bottom row, where you have placed the formula, is not a Footer row.

     

    "Footer row" has a specific meaning in Numbers. It does not mean " the bottom row," although if there is only one Footer row, that is where it will be located on the table.

     

    Numbers tables may have one or more Footer rows, to a maximum of five. To add a Footer row to a table, go Table (menu) > Footer rows > 1.

     

    After adding the Footer row, copy the formula from the cell containing it, then paste it into the Footer row cell in that column.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,205 points)

    "I have 10 separate sheets with all data starting in cell D7.  I amended your formula to be =offset(D7,Count(D),0).  When I placed your formula in B4 the error came back: This formula contains an invalid refernce.

     

    "I have gone back and re-entered your formula in the footer of 4 of the sheets.  In all 3 of them a 0 is returned.  In one of them the value 10 up from the bottom of the column is returned."

     

    COUNT(D) counts all of the numerical entries in regular cells of column D. "regular" cells means any cells in the column that are not in Header rows or Footer rows.

     

    Test 1: Replace =offset(D7,Count(D),0) with =COUNT(D). Compare the result with the  number of data entries in and below cell D7. The OFFSET formula will return the value in the cell that many rows below D7 (eg. if the count is 1, the OFFSET formula will return the value from cell D8, 1 row below D7). The error message you received suggests the offset is more rows below D7 than there are rows (below D7) in the table.

     

    "In all 3 of them a 0 is returned."

     

    "all" would be 4, not 3. See the note on COUNT above. The formula is likely returning a value from a cell below the last one containing data. Empty cells return a value of zero.

     

    "10 up from the bottom"

     

    OFFSET counts done from the base cell, not up from the bottom row. How does "10 up from the bottom" turn out when expressed as "x down from the base cell". The base cell in the formula as shown above is D7.

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (29,850 points)

    Jim,

     

    If I'd known that you wanted to apply my solution to an Excel Worksheet, I wouldn't have given you the deisgn that I did. I make a habit of writing answers that take advantage of the special features of Numbers, which quite often won't work in an Excel Worksheet. When an Excel document is imported to Numbers the tables will require Numbers-izing to work with my suggestion because, as Barry notes, it is important to be precise with the use of Header and Footer rows.

     

    I'm glad that Barry was here to respond to your comments while I was away.

     

    Jerry

  • trexo Level 1 Level 1 (0 points)

    GOT IT!  While my data started in D7, the word "Balance" is listed in D6.  When I adjust the formula to Offset(D6), Count(D),0 it all works!  Did not consider that Numbers would not have differentiated between words and numbers.  My excel formula did not require that. 

     

    Thank you both for your patience.  I apologize for how long it took to solve this.  I am glad to give you both as many points as I can. 

  • Barry Level 7 Level 7 (29,205 points)

    Hi trexo,

     

    You wrote:

    "While my data started in D7, the word "Balance" is listed in D6.  When I adjust the formula to Offset(D6), Count(D),0 it all works! "

     

    "Did not consider that Numbers would not have differentiated between words and numbers.  My excel formula did not require that."

     

    The change you note above has nothing to do with Numbers differentiating (or not) between words and numbers (it does, or more specifically, the COUNT function does, as noted below). The change you not in the first paragraph is due to the way OFFSET works.

     

    OFFSET does what the name implies: It returns the value in a cell whose position is offset a specified number of rows and a specified number of columns from a base cell.

     

    The syntax is: OFFSET(base,row-offset,column-offset)

     

    In your original formula, the base was cell D7, the row-offset is the COUNT of (numerical) data entries in column D, and the column offset is zero.

     

    Suppose that only one data entry had been made (in cell D7). COUNT counts that and returns the result (1) to OFFSET. OFFSET now sees:

     

    OFFSET(D7,1,0) and knows it is to return the value in the cell that is one row below and zero rows to the right of the base, D7. It correctly returns the value (zero) in the empty cell, D8.

     

    When you change the formula to OFFSET(D6,COUNT(D),0) you've made a change to the base, not to the COUNT.

     

    COUNT now counts the single numerical data entry in column D (the one in D7), and OFFSET knows it is to return the value from the cell that is one row below and zero rows to the right of the base, D6. It correctly returns the value in D7, one row below and zero columns to the right of the (new) base, D6.

     

    MORE on COUNT and the 'Excel formula':

     

    COUNT ignores text values and counts only numeric values (including Date and Time values, Durations and numbers formatted as currency, etc.)

     

    You Excel formula was: =index(D:D, Match(9.9999999999999E+307,D:D))

     

    This formula does not use COUNT.

    It 'ignores' text values only to the extent that it assigns them a numeric value of zero.

    MATCH, given no parameter to specify what type of match to attempt, returns the position in a list of the closest match it can find for the search value (9.9999999999999E+307). "Closest match" here means 'the largest value equal to or smaller than' the search value.

    If the list of values in column D is ordered so that the last value is also the largest value (less than or equal to the search value), the formula will return the last value in the list. (see column D, below).

    If the list is ordered in any manner that places the largest value (less than or equal to the search value), then the formula will NOT return the last value in the list. It will return the largest value (less than or equal to the search value). (see column C, below).

    Since the search value in the formula is essentially 10^308 (in standard notation, 1 followed by 308 zeros), it's fairly safe to say the formula will return the largest value in the list.

    Picture 18.png

    Col D formula shown at top of image.

    Col C formula is the same, with C substituted for D in both places.

     

    Regards,

    Barry

Previous 1 2 Next