none
Remove top and botttom values from array RRS feed

  • Question

  • Hi. I have a single dimension array with varying number of values. As long as there are at least three values in the array, I want to remove the highest and lowest value. If the lowest value is 0, and there are several 0's in the array, I just want to delete one of them. I would like to sort the array, and remove the top and bottom values. Can someone please show me how this is done in VBA? Thank you.

    RS

    Thursday, December 26, 2019 9:06 PM

Answers

  • Wow!  Thank you so much.  This does exactly what I need.  Thanks for providing so much clarity on this routine.

    RS

    • Marked as answer by RichardSparrow Friday, December 27, 2019 2:01 PM
    Friday, December 27, 2019 2:01 PM

All replies

  • Wow!  Thank you so much.  This does exactly what I need.  Thanks for providing so much clarity on this routine.

    RS

    • Marked as answer by RichardSparrow Friday, December 27, 2019 2:01 PM
    Friday, December 27, 2019 2:01 PM
  • To:  RichardSparrow
    re:  customizing data

    FWIW, since you already have an answer...
    Excel provides a TrimMean function available on the worksheet and in VBA.
      =TRIMMEAN(array, percent)
    It will calculate the Average of a range or array, as if X percent of the highest/lowest values were removed from the data.
    The percent removed is divided evenly across the largest and smallest values.

    Also, I want to point out that my free Custom_Functions add-in includes a "TrimFunctions" function.
    It works the same as TrimMean for 19 different Excel functions, including , Standard Deviation, Large/Small, Skew, Sum and Var

    Download from MediaFire...  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents
    There is also a Word.doc read me file for download.

    Saturday, December 28, 2019 1:44 AM