+ Reply to Thread
Results 1 to 8 of 8

Avarage and standard deviation of the last 125 rows of the column

  1. 10-12-2021, 02:11 PM #1
    Rockraizer
    Rockraizer is offline
    Registered User
    Join Date
    10-12-2021
    Location
    Budapest
    MS-Off Ver
    2019
    Posts
    9

    Question Avarage and standard deviation of the last 125 rows of the column

    Hello Everyone!

    I have been trying to solve the following problem for 1 day.

    I have an excel worksheet where I need to calculate the last 125 rows of the column. I need the calculate the avarage and the deviation of them.

    I link there my solution: +AVARAGE(OFFSET($A$1;IF(COUNTA(A:A)<125;COUNTA(A:A);COUNTA(A:A)-125);;125)
    +STDEV(OFFSET($A$1;IF(COUNTA(A:A)<125;COUNTA(A:A);COUNTA(A:A)-125);;125)

    My problem is when I try to use this I just realized when there is a blank cell problems occur in the calculation. How can I ignore the blank rows?
    Attached Files Attached Files
    Last edited by Rockraizer; 10-12-2021 at 04:04 PM.
    Reply With QuoteRegister To Reply
  2. 10-12-2021, 02:28 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Avarage and standard deviation of the last 125 rows of the column

    Try these formulae instead. They don't use COUNTA to determine how many rowa there are AND they're not volatile.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 10-12-2021, 03:31 PM #3
    Rockraizer
    Rockraizer is offline
    Registered User
    Join Date
    10-12-2021
    Location
    Budapest
    MS-Off Ver
    2019
    Posts
    9

    Re: Avarage and standard deviation of the last 125 rows of the column

    Thanks the help. I've got error. Maybe because of the 9.9E+307 in the match formula. What can I type there?
    Reply With QuoteRegister To Reply
  4. 10-12-2021, 03:40 PM #4
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Avarage and standard deviation of the last 125 rows of the column

    You probably need to replace the commas by semi-colons for your regional settings.
    Reply With QuoteRegister To Reply
  5. 10-12-2021, 04:03 PM #5
    Rockraizer
    Rockraizer is offline
    Registered User
    Join Date
    10-12-2021
    Location
    Budapest
    MS-Off Ver
    2019
    Posts
    9

    Re: Avarage and standard deviation of the last 125 rows of the column

    Thank you! It's working! Now I see where I faild!
    Reply With QuoteRegister To Reply
  6. 10-12-2021, 04:14 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Avarage and standard deviation of the last 125 rows of the column

    You're welcome.
    Reply With QuoteRegister To Reply
  7. 10-12-2021, 04:18 PM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Avarage and standard deviation of the last 125 rows of the column

    This is the updated file for reference.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 10-14-2021, 04:36 AM #8
    Rockraizer
    Rockraizer is offline
    Registered User
    Join Date
    10-12-2021
    Location
    Budapest
    MS-Off Ver
    2019
    Posts
    9
    Quote Originally Posted by TMS View Post
    This is the updated file for reference.
    Thanks for that too! I want to ask a last question. How is the function exactly working? Can you describe it me? Because I don't understand some part of it and I want to know how is it working?
    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. Standard Deviation Across Column Fields
    By tbregan17 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-09-2018, 04:06 PM
  2. Calculate Standard Deviation corresponding to a category in another column
    By kalikali in forum Excel General
    Replies: 1
    Last Post: 09-11-2015, 01:43 PM
  3. Standard Deviation and Count for multiple subsets within a column
    By alexandra53190 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 05-27-2015, 06:28 PM
  4. Calculate the average and standard deviation for every other cell in a column
    By wyatt in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-04-2013, 11:02 PM
  5. Calculate standard deviation for column based on column heading
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 04:25 PM
  6. Standard Deviation of a column.
    By jebckr in forum Excel General
    Replies: 2
    Last Post: 02-02-2010, 01:44 AM
  7. Individual Standard Deviation Values for Each Column
    By Maurice. in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-14-2009, 11:48 PM

Tags for this Thread

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