hi
I have a table
and I want to know the highest Row number and the lowest Row number (not the data I added, the number of the row(excel) ).
for this, I use =MAX(ROW(table)) but it only shows the 1st row number of the table ! not the last one
can anyone help me with this?
I attached a picture and an excel file as well
thanks
Last edited by mhmahdi12; 03-08-2024 at 04:05 AM.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Well..
=MIN(ROW(table))
Hi, thanks for replying, I wrote the topic wrong(I will correct it after your reply thanks), I am writing the formula as you are saying, but it doesn't work.
Attach a file and we will be able to SEE what work.
Please see post #2 - I am waiting for a sample workbook.
I did, thanks for helping me
MAX, MIN, header, all there.
Formula:
Please Login or Register to view this content.
thanks again for your time
I opened your file(thank you for it) and it was there, but when I copy your formula in the lower cell it does not work again !!
is something wrong with my excel ??
Why don't you just make the range into a proper Excel table? Then rows will be added automatically when you add rows.
In L7 I have created a real table for you to show you what I mean. Type anything into the cell below the table and that row will get aded to the table.
Last edited by AliGW; 03-04-2024 at 08:35 AM. Reason: Workbook added.
You can see from cell C16 that the formula is an array formula, as there are curly brackets around the formula.
You should ensure that your copy of the formula is also an array formula - select the cell, then press function key F2, then hold down Ctrl and Shift and press Enter.
Hope this helps.
Pete
The formula will return the number of the last row of THE TABLE, and will not consider anything bellow it. To get the row number of the 'banana' cell you need to extend the named table range.
See the file attached.
Excel 365 (Windows) 64 bit
Food Table Pizza Burger Fries Soda Ice Cream Banana =MÁXIMO(LIN(Food_List[Food Table])) Header Row Min Row Max Row
Sheet: Sheet1
Well noticed Pete_UK!
Thanks For Replying AliGW
I want to do something else, I do not have any problems with the table. I had problem with the formula that does not work. I want excel itself to calculate and know the min row and max row of a selected range of the table and then use these min and max row numbers in other formulas I want to use.
I do not know if I can share a link here or not, but actually, I am trying to create this excel that this link is creating but I stopped at the beginning because of this problem.
youtube.com/watch?v=eKyAOjH3Crk[/url]
if you see 34:30 to 35:00 you will understand what I want to do.
Again, thank you all for spending your time and helping me.
But as DJ has said, some of the items are not inside the table. Your 'table' doesn't work properly - you need to recreate it as I have done.
I shan't be watching the YouTube - it seems everyone understands what the problem is - you just need to rectify it. A formula will never work as it stands, but it will on the REAL table I created.
wow, thanks Pete, this was the key !
thank you
and a special thanks to DJunqueira and ALIGW too
you are amazing guys
thank you all
You're welcome - thanks for the rep.
If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.
Pete
Tks for the feedback, glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks