+ Reply to Thread
Results 1 to 6 of 6

Finding the number of rows and using that value in a function that uses $

  1. 08-10-2018, 06:16 AM #1
    bebiba
    bebiba is offline
    Registered User
    Join Date
    11-03-2017
    Location
    Wilmington, DE
    MS-Off Ver
    2013
    Posts
    29

    Finding the number of rows and using that value in a function that uses $

    Hi.

    I have a spreadsheet that calculates the percentile of each row based on a filter from another column.
    The formula (in column E in my sample) looks like
    HTML Code: 
    I would like to have Excel determine the number of rows in the table for use in C$20 and D$20 (which I know I can get by using CountA), however I don't know how to incorporate the CountA as part of the $ reference

    Right now I am manually changing the 20 to the number of rows whenever the spreadsheet has data added or removed. The data in the real spreadsheet has thousands of rows and changes every day.

    Is there any way to do this? I can continue doing it manually if I must. Thanks for any guidance in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-10-2018, 06:29 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,426

    Re: Finding the number of rows and using that value in a function that uses $

    The easiest way (I think):

    1. Select your current table.
    2. Go to Insert | Table - this will convert the range to a table.

    You can then use this:

    =IF([@dbID]<>"", PERCENTRANK.INC(IF([Set]=[@Set],[CalculatedRanking],""),[@CalculatedRanking]), "")

    Add rows and the formula will grow with your table. Sample attached.
    Attached Files Attached Files
    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.
    Reply With QuoteRegister To Reply
  3. 08-10-2018, 06:48 AM #3
    bebiba
    bebiba is offline
    Registered User
    Join Date
    11-03-2017
    Location
    Wilmington, DE
    MS-Off Ver
    2013
    Posts
    29

    Re: Finding the number of rows and using that value in a function that uses $

    Wow! Thanks so much... and you taught me how to use @ in the process!
    Reply With QuoteRegister To Reply
  4. 08-10-2018, 06:49 AM #4
    bebiba
    bebiba is offline
    Registered User
    Join Date
    11-03-2017
    Location
    Wilmington, DE
    MS-Off Ver
    2013
    Posts
    29

    Re: Finding the number of rows and using that value in a function that uses $

    how do i mark your answer as the solution?

    nm... found it! thanks again
    Last edited by bebiba; 08-10-2018 at 06:52 AM.
    Reply With QuoteRegister To Reply
  5. 08-10-2018, 06:49 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,426

    Re: Finding the number of rows and using that value in a function that uses $

    You're very welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  6. 08-10-2018, 06:50 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,426

    Re: Finding the number of rows and using that value in a function that uses $

    Quote Originally Posted by bebiba View Post
    how do i mark your answer as the solution?
    It doesn't work like that here - you just mark the thread as solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding number of rows
    By projecttoday in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2011, 03:08 PM
  2. multyplication by finding number from rows
    By vinumv in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-14-2008, 02:22 PM
  3. Finding Number of Data Rows in a Sheet
    By Rabi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2007, 04:58 AM
  4. [SOLVED] Finding the number of rows on a worksheet
    By Jonathan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-19-2005, 10:05 AM
  5. [SOLVED] Finding the minimum in a selected number of rows of the same colum
    By Mark Rugers in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  6. [SOLVED] Finding the minimum in a selected number of rows of the same colum
    By Mark Rugers in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Re: finding the number of rows and the first row with a specific value
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 PM
  8. [SOLVED] finding the number of rows and the first row with a specific value
    By Bruce Bowler in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1