Creating a script to take the medain of a row across multiple columns?

philham01

Forums

Hello -
I am new to IDEA, and was hoping someone could assist me.  I have files in a similar format as below.  What I need to do is take the median of Amt1 - Amt5 for each value in the Text column.  I created the median field using the excel function and was hoping I would be able to do the same in IDEA.
I've been able to compute the median for individual columns, but have not yet figured out a way to do it across a row.
Thank you in advance.
 

Brian Element Tue, 05/19/2015 - 20:56

Hi Phil,

In this instance you would probably need a script to do this on.  Do you always have 5 fields or could the number of fields be variable?

Thanks

Brian 

philham01 Wed, 05/20/2015 - 09:07

Hey Brian,
Thank you for your response.  The number of fields will always be consistant.  Do you have any ideas on how to script this?

Brian Element Wed, 05/20/2015 - 18:30

Ok, here is the first try at this.  The field names are defined in the array sFieldNames().  The script ignores 0 entries when calculating the median.  Let me know if this is close or not.

philham01 Tue, 05/26/2015 - 13:23

Thanks Brian.  This is actually working very nicely.  It works great if there are an odd number of fields being used.  The only issue I've seen is when there is an even number.  The macro pulls the lowest of the two median numbers.  For example 1, 3, 7, 9.  In this scenario the macro will return 3 as the median instead of 5.