Calculate average from range above a threshold

by

AVERAGE_ABOVE_THRESHOLD

749.333333333
610.66666667
1511.33333333
11
8

In this example, we’ll create a custom formula that calculates the average of a range of numbers, excluding any values that are less than a specified threshold.

Scenario: You have a range of numbers in column A and a threshold value in cell B1. You want to calculate the average of the numbers in column A that are greater than or equal to the threshold value in B1.

Here are the steps to achieve this:

Step 1: Setting up the Spreadsheet

  • Create a new Google Sheets document.
  • Enter your data in column A, starting from A2 (A1 will be used for the threshold value).
  • Enter the threshold value in cell B1.

Step 2: Writing the Google Apps Script Code

  • Click on “Extensions” in the top menu, then select “Apps Script”.
  • Delete any default code and replace it with the following script:

// Custom formula to calculate the average of values above a certain threshold

function AVERAGE_ABOVE_THRESHOLD(range,threshold){

let sum = 0;

let count = 0;

for (let i = 0; i < range.length; i++) {

if (range[i] >= threshold) {

sum += range[i];

count++;

}

}

if(count>0){

return sum /count;

}else{

return “No Values above Threshold”;

}

}

Step 3: Using the Custom Formula in Google Sheets

  • Go back to your Google Sheets document.
  • In a cell where you want the result to appear (let’s say cell C1), enter the following formula:

=AVERAGE_ABOVE_THRESHOLD(A2:A, B1)

Explanation of the Code:

  • The function AVERAGE_ABOVE_THRESHOLD takes two parameters: range and threshold.
    • range: This parameter represents the range of cells containing the numbers you want to consider for the average calculation.
    • threshold: This parameter is the threshold value specified in cell B1.
  • Inside the function, we initialize sum to store the sum of values and count to keep track of the number of values above the threshold.
  • We loop through each value in the range and check if it’s greater than or equal to the threshold. If it is, we add it to the sum and increment the count.
  • After looping through all values, we calculate the average by dividing the sum by the count.
  • If no values are above the threshold, the function returns the message “No values above the threshold”.

Step 4: Testing the Custom Formula

  • Enter some numbers in column A starting from A2.
  • Enter a threshold value in cell B1.
  • Use the custom formula in cell C1 to calculate the average of values above the threshold.

Remember that using custom formulas with Google Apps Script requires enabling the “Google Apps Script” extension in your Google Sheets document. Also, make sure that your custom function name (AVERAGE_ABOVE_THRESHOLD) matches exactly with what you use in the formula.

  • Sheet Name: Let’s assume you’re working in the default “Sheet1”.
  • Column A: This column will contain the list of numbers you want to calculate the average of. Let’s say you have the following numbers in column A, starting from A2:

|   A   |

|——-|

|  10   |

|  15   |

|  5    |

|  20   |

|  8    |

  • Cell B1: This cell will contain the threshold value. For this example, let’s assume you have set the threshold to 10 in cell B1.

|   B   |

|——-|

|  10   |

  • Cell C1: This is where you will use the custom formula to calculate the average of values above the threshold. Enter the formula below in cell C1:

=AVERAGE_ABOVE_THRESHOLD(A2:A, B1)

With this setup, the custom formula will calculate the average of numbers that are greater than or equal to the threshold (10 in this case), which should result in an average of 15.

Please note that for the custom formula to work, you need to follow the steps outlined in the previous response to create the Apps Script code and enable the extension in your Google Sheets.

Sheet Name: Sheet1

Column AColumn B
1010
15
5
20
8

Explanation:

  • Column A contains the list of numbers you want to calculate the average of.
  • Column B contains the threshold value, which is 10 in this example.

Formula: In cell C1, you will use the custom formula to calculate the average of values above the threshold.

Cell C1
=AVERAGE_ABOVE_THRESHOLD(A2:A, B1)

With this setup, the custom formula will calculate the average of numbers in Column A that are greater than or equal to the threshold (10). The result should be an average of 15 ([(10 + 15 + 20) / 3]).

Scenario: You have a range of numbers in column A and a threshold value in cell B1. The goal is to calculate the average of the numbers in column A that are greater than or equal to the threshold value in B1.

Steps:

Step 1: Setting up the Spreadsheet

  1. Create a new Google Sheets document.
  2. Enter your data in column A, starting from A2 (A1 is reserved for the threshold value).
  3. Input the threshold value in cell B1.

Step 2: Writing the Google Apps Script Code

  1. Click on “Extensions” in the top menu of Google Sheets, then select “Apps Script”.
  2. Replace any default code with the provided script:

// Custom formula to calculate the average of values above a certain threshold function AVERAGE_ABOVE_THRESHOLD(range, threshold) { let sum = 0; let count = 0; for (let i = 0; i < range.length; i++) { if (range[i] >= threshold) { sum += range[i]; count++; } } if (count > 0) { return sum / count; } else { return "No Values above Threshold"; } }

Step 3: Using the Custom Formula in Google Sheets

  1. Return to your Google Sheets document.
  2. In a cell where you want the result (e.g., C1), input the formula:scssCopy code=AVERAGE_ABOVE_THRESHOLD(A2:A, B1)

Explanation of the Code:

  • The AVERAGE_ABOVE_THRESHOLD function takes two parameters: range and threshold.
  • range represents the cells with the numbers for the average calculation.
  • threshold is the value from B1 that acts as the cutoff point.
  • Inside the function, it initializes sum for the sum of values and count for values above the threshold.
  • A loop iterates through each value in the range. If a value is greater than or equal to the threshold, it adds to sum and increments count.
  • After looping, it calculates the average using sum divided by count.
  • If no values meet the condition, it returns a message.

Step 4: Testing the Custom Formula

  1. Enter numbers in column A (e.g., A2:A) and the threshold value in B1.
  2. Use the custom formula in a cell (e.g., C1) to calculate the average of values above the threshold.

Please note that using custom formulas with Google Apps Script requires enabling the “Google Apps Script” extension in your Google Sheets document. Ensure that the custom function name (AVERAGE_ABOVE_THRESHOLD) matches what you use in the formula.

Google-Apps-Script-Sheets-Formulas-Part-1Download