+ Reply to Thread
Results 1 to 6 of 6

sum of squares after an offset value

  1. #1
    Registered User
    Join Date
    08-20-2013
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    3

    sum of squares after an offset value

    how to find the sum of squares (with an offset value) in excel.

    Suppose i have a table - a1 - a10, then b1 to b10 and so on till e1-e10.

    I want to output in a11 the following -

    sum of ((a1-<number of my choice>)^2 : (a10-<number of my choice>)^2))

    Similarly for b11 through e11. So the answers will be = (4.5-3)^2 +(5.87-3)^2 + (1.25-3)^2 + and so on, 3 here being the offset value.


    each time I want the 11th row to output the sum of the squares of the (number minus an offset value) along a column

    How to do it. Does sumsq have a way of entering the offset value

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: sum of squares after an offset value

    Assuming your offset value is in J2, then try this:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: sum of squares after an offset value

    If you enter it as an array formula (confirm with ctrl+shift+enter) I'd guess you could do:
    =sumsq((A1:A10)-3)

  4. #4
    Registered User
    Join Date
    08-20-2013
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: sum of squares after an offset value

    @conne

    shouldnt it be A1-J2:A10-J2 in each bracket


    @Yudlugar

    i tried what you said - no answer

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: sum of squares after an offset value

    Conne's works as is. Could be simplified to

    =SUMPRODUCT((A1:A10-J2)^2)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    08-20-2013
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: sum of squares after an offset value

    yes his' works - with or without the simplifications

+ 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. Least Squares
    By scantor145 in forum Excel General
    Replies: 1
    Last Post: 01-19-2010, 11:05 AM
  2. Add the squares
    By Zantier in forum Excel General
    Replies: 11
    Last Post: 03-25-2009, 12:05 AM
  3. [SOLVED] chi squares
    By Rozie in forum Excel General
    Replies: 1
    Last Post: 06-29-2006, 05:00 PM
  4. [SOLVED] Squares
    By MSL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2005, 12:05 PM
  5. [SOLVED] removing squares and lines in squares that really should be paragr
    By finnadat in forum Excel General
    Replies: 5
    Last Post: 02-10-2005, 08: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