+ Reply to Thread
Results 1 to 2 of 2

Calculate Standard Deviation spread across N Tabs of Data in Workbook

  1. #1
    Forum Contributor
    Join Date
    09-27-2004
    Posts
    133

    Question Calculate Standard Deviation spread across N Tabs of Data in Workbook

    Excel 2003
    VB 6.5

    I would like to calculate the Standard Deviation (STDEV) for a series of values. These values can be on a variable number, N, of Tabs in a Workbook. Attached is a spreadsheet that shows data, in this case for N=2 Tabs. The STDEV is to be calculated on the Nth + 1 Tab called "Summary" in cell B3. Is there a VBA statement, or series of statements that will make this calculation?

    For example if there was only data on one Tab, the formula in cell B3, after executing the statement would look like the following:

    =STDEV(Inventory340_1!B5:G16,), and if there were two Tabs of data the formula in cell B3, after executing the statement would look like the following:

    =STDEV(Inventory340_1!B5:G16,Inventory340_2!B5:G16)

    The $64,000 question is what statement(s) is required for N Tabs of data.

    I'm hoping to avoid a scenario where I have to cut and paste each set of data and place on one Tab, then perform the STDEV calculations.
    Attached Files Attached Files
    Last edited by scantor145; 01-30-2018 at 03:01 PM. Reason: Additional Information

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Calculate Standard Deviation spread across N Tabs of Data in Workbook

    You can use a 3D reference in the STDEV() function: https://support.office.com/en-us/art...3-c311f47ca173

    The usual approach that I see for N tabs is to bracket the desired tabs with empty "dummy" tabs. So, if you have an empty tab named "start" immediately to the left of the tabs you want to use, and an empty tab named "end" immediately to the right, then you can use =STDEV(start:end!B5:G16). Any tabs you want included in the STDEV() function need to be placed between the start and end tabs.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Replies: 3
    Last Post: 02-08-2016, 05:30 PM
  2. help to calculate standard deviation
    By lana86 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 03-12-2015, 10:27 PM
  3. [SOLVED] How to calculate standard deviation of increasing cumulative gas production data?
    By rbabhi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2013, 03:16 AM
  4. Calculate Standard Deviation
    By goss in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2013, 05:11 AM
  5. Replies: 0
    Last Post: 06-03-2010, 12:01 PM
  6. Replies: 2
    Last Post: 08-23-2007, 03:07 AM

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