If you need multiple validation rules for multiple cells, you should also have multiple ranges for them, so that the rules are tracked independently of each other.
But the second issue, with an illegal choice staying in F after an edit to D, can't be solved with formulas alone. So I wrote a script that keeps better track of validation in column F. Note that it does not set the initial validation rules (before first use); that you can do manually. It also doesn't set rules for column D, which are static.
What it does is the following: when a cell in column D is edited (header row excluded), it adjusts the validation rule of the cell two steps to the right accordingly. Additionally, if that cell contained a value such as OptionB that is no longer allowed when D is blank, the value in F is removed.
In the script, e.range
is the cell edited, e.value
is its new value.
function onEdit(e) {
if (e.range.getColumn() === 4 && e.range.getRow() > 1 ) { // only Column D, not header
var optionA = SpreadsheetApp.newDataValidation()
.requireValueInList(['OptionA'], true).build();
var optionsABC = SpreadsheetApp.newDataValidation()
.requireValueInList(['OptionA', 'OptionB', 'OptionC'], true).build();
var affectedCell = e.range.offset(0,2,1,1); // two steps to the right
if (e.value === '') {
if (affectedCell.getValue() !== 'OptionA') {
affectedCell.setValue(''); // reset if current choice is no longer legal
}
affectedCell.setDataValidation(optionA);
}
else {
affectedCell.setDataValidation(optionsABC);
}
}
}
Short answer
Instead of AVERAGEIF use an array formula as AVERAGEIF requires a range as it's third parameter.
Explanation
The below formula could be used to calculate the average for each category without having to use an auxiliary column and as it use open ended references, it will not require to be modified when new form responses be submitted.
=ArrayFormula(
QUERY(
FILTER(
{'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C},LEN('Sheet1'!A:A)
),
"select Col1,AVG(Col2) group by Col1")
)
Using the example source data provided by the OP, the result is the following:
+---+---+--------------+
| | A | B |
+---+---+--------------+
| 1 | | avg |
| 2 | A | 0.3333333333 |
| 3 | B | 0.75 |
| 4 | C | 0.8333333333 |
| 5 | D | 0.875 |
| 6 | E | 0.7569444444 |
+---+---+--------------+
{'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C}
creates an array with two columns, the first one is the category column, the second calculates the dividend of Column B divided by Column C.
- The FILTER function is used to remove blank rows.
- The QUERY function is used to calculate the average for each category in the first column.
References
Best Answer
What you need to get the desired results would be:
Functions used: