Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How to give range to find the sum

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to give range to find the sum

    I'm doing a statistics workbook there I need to add untill the given value in the cell
    ex: if gave 5 in the cell we want to get the total until only to 5th cells
    (M/M/S)FIFS/INF/N)
    No of Server 3
    l 0.20 n Coeffient P(n) nP(n)
    m 0.4 0 1 0.060657 0 P(0) 0.060657
    q 0.5 1 2.5 0.151643 0.151643 Ls 2.245156
    2 5 0.303286 0.606571
    N 5 3 7.5 0.454928 1.364785
    4 0.416666667 0.025274 0.101095
    5 0.069444444 0.004212 0.021061
    We changing the value in N=5

  2. #2
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to give range to find the sum

    Welcome to the message board!

    I believe the OFFSET-function is what you're looking for.

    Here's a good introduction to the OFFSET-formula: OFFSET formula – Explained | Chandoo.org - Learn Microsoft Excel Online

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to give range to find the sum

    Could you help me little more ................
    I couldn't get it.

  4. #4
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to give range to find the sum

    The OFFSET function does not return a value but a range that you want to use in your actual calculations.

    The syntax is

    =OFFSET(starting point, rows to move, columns to move, height, width)

    Where:
    Starting point is the base range (= usually a single cell). This is the anchor point of your OFFSET function.
    The Rows / Columns to move set how many rows / columns you want to move from the starting point before the actual range begins: OFFSET(B2,-1,1) means the OFFSET-range moves one row up and one column to the right from B2 (=C1).
    The height and width set the actual size of your range: OFFSET(B2,-1,1,5,2) means you want your range to be 5 rows tall and 2 columns wide, starting 1 row up and one column right from B2. The formula returns the range C1:D5.

    =SUM(
    OFFSET(B2,-1,1,5,2)) means you want to SUM the values in your OFFSET range. The static formula for the same thing would be SUM(C1:D5).

    Often times you replace the static rows / columns / heights / widths with formulas returning the desired value (for example COUNT to return how many rows of data there is or MATCH to find a certain value from your list).

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to give range to find the sum

    Thank you very much....................

  6. #6
    New Member
    Join Date
    May 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to give range to find the sum

    How to get the content of a cell to offset function.because in my problem range is determine by N
    if N=5 then I have to get the sum of 5 rows and if N=3 then I have to get the sum of 3 rows like wise it goes on...........

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,414
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    16 Thread(s)

    Default Re: How to give range to find the sum

    Quote Originally Posted by Dili1234 View Post
    How to get the content of a cell to offset function.because in my problem range is determine by N
    if N=5 then I have to get the sum of 5 rows and if N=3 then I have to get the sum of 3 rows like wise it goes on...........
    I can't really see what you are trying to do in post #1, but to sum N rows of a range, you can do as follows. Hopefully you can then apply that to your actual problem, or describe your problem more clearly.

    As suggested by Misca, this is also possible by using OFFSET but that is a volatile function so I would avoid that when there are feasible non-volatile alternatives like below.

    SUM N rows

     ABCD
    1Data NSUM
    21 410
    32   
    43   
    54   
    65   
    76   
    87   
    9    
    10    

    Spreadsheet Formulas
    CellFormula
    D2=SUM(A2:INDEX(A2:A8,C2))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member
    Join Date
    May 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to give range to find the sum

    Thank you.............very much to both of you

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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