Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

StDev of values in column X for all rows with identical values in column Y

0

I am trying to figure out how to have my spreadsheet do a standard deviation calcuation in one column on a range where all the values in another column are identical. 

In other words, I want to be able to locate all the same values in column A, then run a calculation on that range in column B. I have done this historically by checking 3 rows at a time and calculating if all values in column A are identical, but this is not the answer I want. Sometimes there are 3 of the same value, or 5, or 25. 

How can I get excel to recognize a "family" and then do something based on that range?

excel
MS Office Version: 365
posted by: KadieB on Sat Oct 23, 2021 at 2:50 pm
Answer
Discuss

Discussion

Kadie

Thanks for your comment below (under my original Answer, now-deleted since I misunderstood you): 

....I need to calculate standard deviation (or any measure of variability, really) of variable sizes of data in one column. In the attached file, you can see that for every value in column A that is identical, I found the standard deviation of that same range in column B. I am working with thousands of rows of data and 7 or more columns that I want to analyze the variation of, and doing this several times per day, so doing it manually isn't really an option for me.   In future please make such clarifications in the original question so contributors (might answer) and other users understand the full question without digging into discussions). 

Please clarify also:

1) "every value in column A that is identical" - are such cells always adjacent like your sample file or might they be spread through the thousands of rows? 

2) is the data in "7 or more columns" grouped by the same identical values in column A? I.e. the condition is the same for each even though the function(s) might vary?

This feels a working solution would be quite a time-saver for you -  am I right to assume that you're not familiar with VBA?
John_Ru (rep: 6142) Oct 24, '21 at 3:41 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login