Chart/Sort column data from BOTTOM to TOP for generating charts

Question

Level 1

8 points

Chart/Sort column data from BOTTOM to TOP for generating charts

I have a column which I update with the most recent data at the top. Unfortunately, If I want to use this column for generating charts, Numbers puts the most recent (top data) at the left but I want the oldest data (bottom of column) at the left, and the newest data at the right.

I found someone had exactly the same problem https://discussions.apple.com/thread/250304840?answerId=250576301022#250576301022 and solved it, but I can't seem to make it happen for me. It always says that there is a syntax error with this formula.

Any chance, someone can give a better example with not just a written formula but an "active" formula where Numbers marks/shows all the columns and lines in the syntax, so I can figure it out?

Thanks in advance.

Posted on Nov 24, 2019 7:24 PM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,053 points

Dec 2, 2019 11:50 PM in response to lu210

Hi lu,

Nearly there! INDEX and MATCH on row numbers.

In the Data entry table, C2 =ROW()

and Fill down.

In the Data for Graph table,

C2 =MAX(Data entry::C)

C3 =C2−1

and Fill down.

In A2 (a Header Column, because this is a Category graph) Formula = INDEX(Data entry::A,MATCH($C2,Data entry::$C,0))

and Fill down.

We can't drag to fill right from a Header Column into a Body Column, so copy from Column A into Column B.

Formula becomes =INDEX(Data entry::B,MATCH($C2,Data entry::$C,0))

The value for 1 Dec 2019 is USD0.00 (nothing entered yet).

Now enter the value in Data entry B2

Please call back with questions.

Regards,

Ian.

Reply

Answer 2

Yellowbox

Level 7

21,053 points

Nov 24, 2019 8:14 PM in response to lu210

Hi lu,

I will make the same comment that Barry made in that discussion you refer to. We can't see what you see!

Please click on the table to make the row and column labels visible, and post a screen shot that includes the row and column labels.
What type of graph? A scatter plot or a category graph?

Regards,

Ian.

Reply

Answer 3

Yellowbox

Level 7

21,053 points

Nov 24, 2019 9:28 PM in response to lu210

Hi lu,

Thanks for the screen shot and explanation.

I can get the INDEX formula to do something, but the results make no sense

.

INDEX(B,COUNTA(B,0))−ROW()+2

One suggestion is to copy the data to another table, sort ascending by the data column and use that table for the graph.

Regards,

Ian.

Reply

Answer 4

lu210 Author

Level 1

8 points

Dec 2, 2019 6:44 PM in response to Yellowbox

Thanks for the help and reply Ian!

Unfortunately, sorting ascending with manually copying is not really practicable in my case.

I have 7 charts to update in a 2-day interval and an automatic update from the charts would be sure preferred :)

Reply

Answer 5

t quinn

Level 5

7,056 points

Dec 16, 2019 6:45 PM in response to lu210

Hi lu,

Numbers is designed with a preference for new data being entered at the bottom of the tables. It is usually possible to create workarounds so that you can do things just like you want to do them but it can take its toll. You could find that adding new rows of data at the end of your table is not so hard to get used to.

quinn

Reply