2

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);
}

2 Answers 2

3

you can wrap it into IFERROR() to set custom #NUM! (in your case: if #NUM! then blank)

=IFERROR(ARRAYFORMULA(LARGE(IF((D:D="cc")*(E:E="denied"),B:B),1)),)
1
  • 1
    you're the best! this worked perfectly :) THANK YOU Aug 13, 2018 at 4:09
0
=MAXIFS(B:B, A:A, "x", D:D, "email", E:E, "denied")

this one works as well if you are willing to play with Conditional formatting in this way:

1
  • 1
    didn't work for me, even though the cells were formatted as a date. not sure why. but your 2nd answer worked like a dream. thank you! Aug 13, 2018 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.