Hi All
I wonder if someone can help me with adding a new column to my spreadsheet that calculates and shows the position change of a row.
e.g. when data in the table changes and it's re-sorted, if row 5 moves up to row 3 the new column will display +2 or conversely -2
Any help would be truly appreciated.
Here is my sheet (Excel 2013)
Many thanks
acumendev
Try in N5:
Formula:
Please Login or Register to view this content.
and sort
before next attempt to sorting fill again column 1 with 1,2,3...
PS. Note that while you were defining table you had "my table has headers" unchecked, so Excel treated Club, Name, Played, etc. as first row of data and not as headers
Best Regards,
Kaper
Re: League Table Movement
Thanks Kaper for the quick solution - I've added it in and it works exactly as I wanted. The only thing I didn't think about was the league position number for display purposes, but I created a new column (2) which I manually fill with 1,2,3 etc ascending.
My final question, is it possible for the positive movement upwards e.g. +2 to display the '+' sign or not?
Sorry there is a question 2 - not sure how to change the table to 'my table has headers' from how it is now?
thanks once again
acumendev
As for formatting - use custom formatting with: "+0,-0,0,@" for this column cells (or may be:"+0;-0;;@" to show simply nothing when the position is the same)
As re-creating table but with headers - mark it, right-click and from pop-up Table-> convert to range, and create again with checked "my table has headers" option.
That is excellent - thank you Kaper.
Is it possible for you to explain what is happening in your original formulaso that I can gain a better understanding of it please?Formula:Please Login or Register to view this content.
Thank you
acumendev
Well, [@Column1] is a structural adressing of the cell which is in Column1 in current row
row() is worksheet function, which returns current row number
your table started from row 5 (you had there 1 so to get 0 if nothing is changed -4 finalizes the formula.
Nice tool for analyzing what happens inside particular formula is in Formulas->Formula Auditing->Estimate Formula
Wow! Kaper you've not only helped me to learn some new stuff I was struggling on, but also pointed me in a couple of other directions that I can research - many many thanks
regards
acumendev
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks