Calculating Median

[Cameraville]

Can anyone provide an efficient way to calculate median? I have a list of ‘things’ with relations to their various prices and I would like to show the median price for each thing. I have used rollups to easily get min, max, and average. Median is proving difficult but useful for my users. I am an experienced user. Thank you.

[Darren_Murphy]

Just thinking out aloud…

• Use a Lookup column to create an array of values
• Sort the array
• Determine the length of the array
• Pick out the one in the middle using a single value column

Alternatively, create a Joined List and feed that into a JavaScript column:

function median(list) {
  const arr = list.split(', ').map(Number);
  const sorted = arr.sort((a, b) => a - b);
  const middle = Math.floor(sorted.length / 2);
  if (sorted.length % 2 === 0) {
      return (sorted[middle - 1] + sorted[middle]) / 2;    
  }
  return sorted[middle];
}

return median(p1);

[Cameraville]

Thanks, but there is no middle value if the quantity of value is even.

[Darren_Murphy]

The JavaScript option that I just added should deal with that.

[Cameraville]

Thanks a lot I will check it out. I checked your first message on my phone and it showed no images.

[Cameraville]

So far I have successfully been able to make all my apps with no spreadsheet formulas or custom code - I aim for this over performance concerns. Additionally I do not have experience with javascript and I could not find sufficient documentation for this in glide, so I’m lacking in this area. Could you please show me what the Edit column for Median looks like? I have not been able to get this to work. Thank you very much.

[Darren_Murphy]

Sure, here is what it looks like:

• The column type is JavaScript (just search for it)
• Paste the code that I gave into the Data section
• Pass your Joined List of values as p1

Note that the JavaScript code expects the values to be joined with a comma + space (, ), which is the default for the Joined List column. If you were to change that then you would need to adjust the second line of code. For example, if you got rid of the space and just used a comma, then line 2 would become:

const arr = list.split(',').map(Number);

[Cameraville]

@Darren_Murphy

function median(list) {
  const arr = list.split(', ').map(Number);
  const sorted = arr.sort((a, b) => a - b);
  const middle = Math.floor(sorted.length / 2);
  if (sorted.length % 2 === 0) {
      return (sorted[middle - 1] + sorted[middle]) / 2;    
  }
  return sorted[middle];
}

return median(p1);

Thank you Darren. I can see from your extra info that I had done it right. The problem was that my dataset was prices and had a currency symbol it with resulted in 'NaN’s. I assumed glide would have used them in the joined list for display purposes only - as rollups work just fine with any units. With your code and see help from GTP I accounted was able to account for this, and output the result to the nearest whole number and dsiplay my original number format. It works great thank you.

function formatCurrency(amount) {
  const formatter = new Intl.NumberFormat('en-US', {
    style: 'currency',
    currency: 'EUR',
    maximumFractionDigits: 0,
  });
  return formatter.format(amount);
}

function median(list) {
  if (!list || list.trim() === '' || list === '0') {
    return;
  }
  
  const arr = list.split(', ').map(price => Number(price.replace(/[^0-9.-]+/g, '')));
  const sorted = arr.sort((a, b) => a - b);
  const middle = Math.floor(sorted.length / 2);
  let medianValue;
  if (sorted.length % 2 === 0) {
    medianValue = (sorted[middle - 1] + sorted[middle]) / 2;
  } else {
    medianValue = sorted[middle];
  }
  
  return formatCurrency(medianValue);
}

return median(p1);