I am adding columns to a table. In addition to using the automatic range feature for finding averages, I need to also reference separetly the cells in the last column added.
I can't seem to get that reference to move with each added column - the reference simply stays to the individual cell that was the original last, (while the average does automatically change when I add a column.
Help?
MACBOOK AIR (11-INCH, EARLY 2015), iOS 10.2.1
HI James,
The other questions aren't there just to fill space. Answers would have been useful to prevent having to guess what other factors could be in play, thus reducing the possibility of turning into blind alleys. No blind alleys found, but that could be due to not including the blocks in the sample table.
Here's bare version containing only data in cells that influence the value returned to the target cells G6 and F6.
The formula in D6 uses MATCH, LARGE and INDEX to return the most recent value in column D. The warning flag is thrown by LARGE, whose complaint is 'the formula refers to cells without numbers.'
The formula in F6 uses COUNT and INDEX to return the value in coumn F in the last row containing a number or number-like value in column A. It depends on every row above containing either a number, or a number-like value, and no row below that containing such a value.
New row, Date and values added:
Formulas:
D7: INDEX(D,MATCH(LARGE(A,1),A,0))
F7: INDEX(F,COUNT(A))
Either will work in either location provided the first argument of INDEX is the letter of the column containing the data.
Regards,
Barry
Hi James,
I've copied your screen shot and placed it on an empty table to allow including cell addresses in the image.
Clarification, please:
What is the formula you are having difficulty with? Where is it located?
What is causing the blue warning triangles in E5 and G5? What is the warning?
What is causing the blue warning triangles in D7-D9 and F7-F9? What is the warning?
I am assuming the columns D and E are a set, that columns F and G are a set, and that the two seta are independent of each other. Is that correct?
Which of the 'blank' cells are truly empty (no invisible characters, including no null strings; no formulas)?
What do each of the not-empty 'blank' cells contain? (Include column C and the non-visible cells in column A)
Regards,
Barry
Hi Barry.
The Blue warnings indicate that the cells were auto changed when I transposed the table (previous help was why not have the dates run down the column).
in the current table:
D8 will continue to update as new data rows are entered (row 5 here) Ave D2:D5 will auto expand to include each new row in the D column. I need D7 to continue to "move" down the column as new rows are added so it continually reflects the newly entered data. (I need the "current Balance, new value" to always equal "new day, new Value"
Can you post a screenshot?
Generally, your approach might be to have your values in column B, have a row defined as a Footer Row at the bottom and in column B in the Footer Row have a formula like =AVERAGE(B). Then, when you add rows with new values, the formula will automatically include all values of the body cells of that column, even the newly added values.
SG
Hi Barry,
YES! Thank you!
Thank you for your Help. I apologize for not giving you all of the info. Was my ignorance in trying to better explain my question.
I used F7: INDEX(F,COUNT(A)) and that seems to work for me.
I don't understand the use of MATCH or LARGE...
Is there an easy reference for the use of these?
"I don't understand the use of MATCH or LARGE...
Is there an easy reference for the use of these?"
There's an easy reference built into your copy of Numbers.
Open any Numbers document (a new one from a Blank template is good if you want to explore), then click the Format brush to show the Inspector in the right sidebar.
Click on any cell to open the Formula Editor and open the Function Browser in the Inspector space.
Clicking on any of the Categories in the list will open a list of the functions in that category.
Or you can search for functions by name, using the search box above the list.
Each search result contains the functions whose names fully or partially match the search term and a list of related functions. Click on the name of the one you are interested in.
Besdes the list of related functions, you get a description of the chosen function and what it does, a description of its syntax, and at least one example of its use including sample results.
Double-clicking on the function's name in the list inserts it in the function editor (at the insertion point, which may be in another function or formula), complete with placeholder tokens labelled with what goes there.
If you want something you can browse, download the iWork (09) Formulas and Functions user guide. The guide was written for the '09 versions of the iWork applications, so it doesn't include any of the functions added since then and does include some features missing from current versions, but the larger part is still relevant to Numbers 3 and 4.
Looks like it's too late for that one. It and the Numbers, Pages and Keynote guides for the '09 set are no longer listed in the download manuals. The Function Browser it it, then.
Regards,
Barry
Happy reading,
Barry
The Average works and automatically includes newly added columns.
What I can't figure out is as I add Columns, I always want the "Current Balance" to reflect the last day that data was entered.
.... I still need to calculate the date from the last added (when added) Date...
How about rearranging your table so the dates go down a column rather than across rows?
SG
Excellent! Thank you!
Numbers Automatic Range
