Thread: Excel 2007 Standard Deviation of non-contiguous ranges with max and min removed

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Please read what I wrote - you need to provide links to any cross posts you have made. That's all!

Try this version

=STDEV(SMALL(month1,ROW(INDIRECT("2:"&COUNT(month1)-1))))

confirmed with CTRL+SHIFT+ENTER

[Note that this will remove just a single instance of the MAX and MIN values - not all if those values are duplicated.......]

Ok, the problem is that even when you define a single range to be two columns you still can't use that range in functions that don't accept two columns - in my suggested formula COUNT and SMALL will accept those.

Try defining your ranges differently, e.g. assuming month1 is sheet1 B2:B100 and N2:N100 then change the definition to this [in "refers to" box]

=CHOOSE({1,2},sheet1!$B$2:$B$100,sheet1!$N$2:$N$100)

Now you can use your original formula......

One last thing, please:

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save