+ Reply to Thread
Results 1 to 6 of 6

Selecting a Range based on known cell positions

  1. 04-25-2018, 10:09 AM #1
    im00r3
    im00r3 is offline
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Selecting a Range based on known cell positions

    Hi there,

    Sorry about the thread title - i'm struggling to define what i want :S

    I'm looking at one column data of variable length. I would like to be able to paste this data into an excel sheet and then work with it.

    I know the first cell of data as it's where the data will be pasted say B10.

    I can find the last cell using =LOOKUP(2,1/(B:B<>""),ROW(A:A)) say B746345

    What i would then like to do is perform calculations on the range B10:B746345 for example =averageif(B10:B746345,">"&B7) where B7 is a known value...

    This sounds like it should be do-able. I'd like not to resort to VBA if possible, and if i have to then there will be another thread!

    Thanks for reading this jumble

    Cheers

    iMoo - Revolutionary dairy producer
    Reply With QuoteRegister To Reply
  2. 04-25-2018, 10:23 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: Selecting a Range based on known cell positions

    Try

    =AVERAGEIF(B10:INDEX(B1:B1000,LOOKUP(2,1/(B1:B1000<>""),ROW(A:A))),">" & B7)
    Reply With QuoteRegister To Reply
  3. 04-25-2018, 10:55 AM #3
    im00r3
    im00r3 is offline
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Re: Selecting a Range based on known cell positions

    =AVERAGEIF(B10:INDEX(B1:B1000,LOOKUP(2,1/(B1:B1000<>""),ROW(A:A))),">" & B7)
    Thanks John - That doesn't give me the same answer as when i manually select the range and perform the averageif >B10, so no cigar i'm afraid.

    I think i recognise some of that from Exceljet to find the last cell in the range. The index and Row bits i rarely use, so need to get my head around anyway.

    Should the B1:B1000 should that be B10:B100000 or a value higher than the amount of data points?

    [edit] Seems not - just tested that... Stumped!
    Last edited by im00r3; 04-25-2018 at 12:11 PM.
    Reply With QuoteRegister To Reply
  4. 04-25-2018, 01:25 PM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: Selecting a Range based on known cell positions

    Post a sample file but only about 30 rows!!!

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Last edited by JohnTopley; 04-25-2018 at 01:35 PM.
    Reply With QuoteRegister To Reply
  5. 04-25-2018, 01:28 PM #5
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: Selecting a Range based on known cell positions

    and/or

    see attached.

    Extend data range in column B and see results.
    Attached Files Attached Files
    Last edited by JohnTopley; 04-25-2018 at 01:38 PM.
    Reply With QuoteRegister To Reply
  6. 04-25-2018, 09:06 PM #6
    im00r3
    im00r3 is offline
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Re: Selecting a Range based on known cell positions

    Hi John, That seems to do it - i'm not sure what i did wrong first time, but it might have something to do with it being a silly time at night over here in Myanmar.


    Thanks for your help


    ++kudos
    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. [SOLVED] embeded if statements alternative-Selecting cell based on a range
    By chubs in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-05-2017, 04:28 AM
  2. Selecting a range of columns based on a cell value
    By sa1ntj0hn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-18-2016, 10:05 AM
  3. selecting cell range based on number in a different cell
    By tzvi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2015, 06:44 AM
  4. [SOLVED] Selecting a Range based on location of a cell
    By spcw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-11-2014, 03:09 PM
  5. [SOLVED] Help with selecting a range based on the value of a cell
    By jaredj34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2014, 12:22 PM
  6. Selecting a range based on cell value
    By alpha1980 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-26-2008, 07:19 AM
  7. Selecting and deleting named range based on cell value
    By Rich Kniatt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2006, 03:04 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