Google Sheets:How to update a cell with the range of values in one column, based on the contents of another range of values in different column?

0
votes

enter image description here

In cell K18, I want to see the range of values from column F (Velocity), which should be dependent on the range (same rows) from column G (Peak dB). So something like 37-39 or 37 to 39 to be displayed in the cell K18 (or whichever syntax is best to use for pivot tables and/or charts (maybe a low column and a high column is better for making my chart? I don’t know).

Column G is set to Conditional Formatting, to colour cells falling between multiple different ranges (-9 to-8, -8 to -7 etc)

The =COUNTIFS function in column J shows the total number of hits/occurences within the given range from G column(Peak dB). (I coloured the cells to match the Conditional Formatting colours of Column G manually).

In the end, the outcome i want, is to bring this info into a chart that neatly overlays/stacks the data from columns I (dB Range), J (No of hits) and K (Velocity Ranges), together.

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
If I misunderstood your question, I apologize. Is your sample image is the sample input situation? If my understanding is correct, can you provide the sample output situation? By the way, in your tags, google-apps-script is included. In your goal, Google Apps Script is used? – Tanaike
@Tanaike The image is my spreadsheet in question. I’ve marked the cells of an example of the data range i need (F column), and the target cell (K18) with red outlined rectangles. The data range in K18 that i want, should be obtained from the F column data (Velocity), which is dependent upon the equivalent range of the values in the G column (Peak dB). I included google-apps-script in the tags, in case somebody recommends that as the best way to go. – sunking
Thank you for replying. I have to apologize for my poor English skill. From your replying and your current sample image, I can see that there are 2 "red outlined rectangles" and I understood that you can use Google Apps Script for achieving your goal. Do you want to put a value to a cell "K18"? If my understanding is correct, what value do you want to put? And, in your sample image, do you want to put the values for other cells of the column "K"? I deeply apologize for my poor understanding. – Tanaike
@Tanaike Yes I want to put a value in K18. From the example image included, that value should be '37 to 40' - that is cells F11 to F17. This should be determined as a result of the range G11:G17. And also, yes I want to have the other values in other cells in column K, reflecting the other ranges in column F, dependent on column G. – sunking
Thank you for replying. From your replying, I understood that you want to put the value of 37 to 40 to the cell "K18", and you want to also put the values for other cells in the column "K". Although I understood about the cell "K18", I cannot understand about the values for other cells. Because I cannot understand your logic for retrieving the values. I apologize for this. But I would like to try to understand about your question. When I could correctly understand about it, I would like to think of the solution. I apologize that I cannot resolve your issue soon. – Tanaike

1 Answers

1
votes

Given the sample data I'd suggest you to use Google Apps Script

Here's my approach:

function processVelocityRanges() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  
  let dataLength = sheet.getDataRange().getValues().length; // Get number of rows
  let velocityRange = sheet.getRange(`F2:F${dataLength}`).getValues(); // Column F
  let peakDBRange = sheet.getRange(`G2:G${dataLength}`).getValues(); // Column G
  let dbRange = sheet.getRange(`I2:I${dataLength}`).getValues(); // Column I

  for(dbRangeIndex = 0; dbRangeIndex < dbRange.length; dbRangeIndex++) {
    let value = dbRange[dbRangeIndex][0];
    if(value) {
      let splitValue = value.split(" to ");
      let lesserValue = Number.parseInt(splitValue[0]);
      let greaterValue = Number.parseInt(splitValue[1]);
      
      // Get the velocity range
      let velocityRangeText = getVelocityRange(lesserValue, greaterValue, velocityRange, peakDBRange);
      if (velocityRangeText) {
        let rowToChange = dbRangeIndex + 2;
        // Change the text in K column
        sheet.getRange(`K${rowToChange}`).setValue(velocityRangeText);
      }
    }
  }
}

function getVelocityRange(lesserValue, greaterValue, velocityRange, peakDBRange) {
  let velocityValues = [];
  for(i = 0; i < peakDBRange.length; i++) {
    if (peakDBRange[i][0] <= greaterValue && peakDBRange[i][0] >= lesserValue) {
      velocityValues.push(velocityRange[i][0]);
    }
  }

  if (velocityValues.length <= 1) {
    return "";
  }

  return `${Math.min(...velocityValues)} to ${Math.max(...velocityValues)}`; // destructuring assignment
}

First of all retrieve the ranges where you have to make comparisons, then using these make a simple search. This code only provides text if there are at least 2 values in order to create a "min TO max" text. Otherwise it just returns an empty string from getVelocityRange function and doesn't set any value on your Sheet.