I am using the MAXIFS formula to find the max date if criteria are met. However, when the criteria are NOT met, it is giving a max date of 12/30/99.
How can I get this to be blank when the criteria are not met?
My formula: =maxifs(B:B, A:A, "x", D:D, "cc", E:E, "denied")
The issue: When "denied" is missing from column E, the result is 12/30/99. How can I get it to be blank?
EDIT: I realized the result is 12/30/99 because the cell format is date.
Is there any way to get the MAXIFS formula to be blank when criteria are not met? Or is this a find/replace sort of thing?
I can also use an array formula =ArrayFormula(LARGE(IF((D:D="cc")*(E:E="denied"),B:B),1)), but noticed the array doesn't auto-update when all criteria are met. When criteria are not met (ie column E doesn't have denied, then I get an error: NUM
I tried adding a script to run a find/replace onEdit (found this from another StackExchange Q&A, but it made all cells within my range the same date.
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("inquiries.app date.bonus");
var range = ss.getRange("L2:O4");
var data = range.getValues();
var formulas = range.getFormulas();
for (var i=0;i< formulas.length;i++) {
if(typeof formulas[i] !== "undefined" && formulas[i] != ""){
formulas[i][0] = formulas[i][0].replace(12/30/99, "");
data[i][0] = formulas[i][0].toString();
}
}
range.offset(0, 1).setValue(new Date())
}
This script worked but changed my cell format to automatic. Is there a way to make sure cells are formatted as date?
function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("inquiries.app date.bonus");
var range = ss.getRange("L2:O4");
var data = range.getValues();
var formulas = range.getFormulas();
for (var i=0;i< formulas.length;i++) {
if(typeof formulas[i] !== "undefined" && formulas[i] != ""){
formulas[i][0] = formulas[i][0].replace(12/30/99, "");
data[i][0] = formulas[i][0].toString();
}
}
range.setValues(data).setNumberFormat("0");
}
I tried these formulas, but nothing happened. In fact, it removed the cell formulas and changed it to values.
function onEdit(e) {
var s = e.source.getActiveSheet();
if (s.getName() !== 'inquiries.app date.bonus' || e.value !== '12/30/99') return;
e.range.setValue(s.getRange('never')
.getValue());
}`
and
`function replace(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("inquiries.app date.bonus");
var range = ss.getRange("L2:O4");
var vlst=range.getValues();
var i,j,a,find,repl;
find="12/30/99";
repl="never";
for (var i = 0; i < vlst.length; i++) {
for (var j = 0; j < vlst[i].length; j++) {
a = vlst[i][j];
if (a == find) vlst[i][j] = repl;
}
}
range.setValues(vlst);
}
and
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("inquiries.app date.bonus");
find="12/30/1999";
repl1= "never";
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("inquiries.app date.bonus");
var range = ss.getRange("L2:P4");
var vlst=range.getValues();
var i,j,a,find,repl;
repl= repl1;
for (i in vlst) {
for (j in vlst[i]) {
a=vlst[i][j];
if (a==find) vlst[i][j]=repl;
}
}
range.setValues(vlst);
}
