+ Reply to Thread
Results 1 to 11 of 11

Part of the range formula

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    Donetsk, Ukraine
    MS-Off Ver
    Excel 2013
    Posts
    4

    Smile Part of the range formula

    Hi,

    Is there any way to get some part of the range to use in some formulas like SLOPE?
    If I have a range, for example, A1:A500, is it possible to get somehow only first element and the last 20, so it forms the range of only 21 elements?

    =SomeFormula(A1:A500) and it returns only A1 and A480:A500, not using vba.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Part of the range formula

    Well, if I was adding up values in A I could use

    =SUM(IF(ROW(A1:A11)={1,9,10,11},A1:A10))

    (Entered as an array using Ctrl+Shift+Enter)

    and it would add the first row, 9th, 10th, and 11th only. This illustrates how criteria can be applied to a range to selectively choose certain values.

    You would still need Y Coordinates to apply this to SLOPE though.


    Does this type of range limitation seem along the lines of what you're looking for? Perhaps this formula could be edited to include the first and last X rows.

  3. #3
    Registered User
    Join Date
    08-16-2013
    Location
    Donetsk, Ukraine
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Part of the range formula

    Thank you for the answer. It seems this is what I'm looking for,
    but this formula returns an error #N/A, can't figure out why.
    And yes it would be nice to not use some predifined rows as {1, 9, 10, 11} but some X first and X last.
    Is it possible?

  4. #4
    Registered User
    Join Date
    08-16-2013
    Location
    Donetsk, Ukraine
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Part of the range formula

    I also tried like that:
    {=IF(ROW(A1:A11)>5;A1:A11)} - this works ok (returns the values of the rows which are more than 5)

    but I need something like that:
    {=IF(OR(ROW(A1:A11)>5;ROW(A1:A11)<3);A1:A11)} - this doesn't work (returns all values, but should only values more than 5)

  5. #5
    Registered User
    Join Date
    08-16-2013
    Location
    Donetsk, Ukraine
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Part of the range formula

    Sorry, there was no error in the first formula, I just had "," as a delimeter in my regional settings.
    So =SUM(IF(ROW(A1:A11)={1,9,10,11},A1:A10)) works just fine.

    But I still have problems with that, I have to copy about 20000 formulas like that and the parameters in {1,9,10,11} can vary.
    Is there a way they can change as well as the numbers in A1:A10 (when I copy it in the next cell they become A2:A10 and so on).

    Thank you.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Part of the range formula

    instead of hard-coding those values, you could put them in their own cells and then reference them...
    =SUM(IF(ROW(A1:A11)={J1,J2, J3, J4},A1:A10)) where J1=1, J2=9, J3=10, J4=11
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Part of the range formula

    I'm having issues calling on references within braces. Am I missing something?

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Part of the range formula

    You need to do it like this (again, array-entered):

    =SUM(IF(ISNUMBER(MATCH(ROW(A1:A10),J1:J4,0)),A1:A10))

    Braces only apply to array constants; if you want to reference an array of cells or ranges, it needs to be contiguous (and no braces required).

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Part of the range formula

    Awesome! Therefore, the SLOPE adaptation would be:

    =SLOPE(IF(ISNUMBER(MATCH(ROW(A1:A500),E5:E25,0)),A1:A500),IF(ISNUMBER(MATCH(ROW(B1:B500),E5:E25,0)),B1:B500))

    (Entered as an Array)

    Where A1:A500 is X values, B1:B500 is Y values, and E5:E25 contains the values for the rows you want to include.

    Stas_p, see attached.

    Attachment 259012

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Part of the range formula

    Thanks for the assist XOR

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Part of the range formula

    You're welcome, Mr Dibbins.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Part of formula with named range is missing (?)
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2013, 08:27 PM
  2. Part of formula with named range is missing (?)
    By ratkins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2013, 08:20 PM
  3. Using Range Name reference as part of R1C1 Formula
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-14-2012, 01:32 PM
  4. Lookup a total from a range to use as part of a formula
    By SpockIOM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-19-2010, 10:21 AM
  5. Changing part of a formula in a range of cells
    By Glyndo in forum Excel General
    Replies: 5
    Last Post: 04-20-2009, 05: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1