Excel Formula returning the smallest value, taking into account empty cells

2
votes

I have 4 dates across a table, I want to return the smallest date so have used the below, however if that cell is blank I want the cell displaying the date to be blank? This is what I've tried:

=SMALL(D2:K2,1,D2,"")

It does not work.

5
arraysexcelexcel-formula
Care to show some sample data and expected return? For now, you have used more parameters than allowed > SMALL(Matrix, nth smallest). Reason I'm asking is that D2:K2 holds more cells than possibly 4 dates. – JvdV

5 Answers

1
votes

I am not sure if I understand your question correctly.

If you wish to display the lowest date in the range or - if any of the cells are blank - a blank cell, you might use this formula. It checks if the number of non-blank cells is 0 and results either in the minimum date or a blank.

=IF(COUNTBLANK(D2:K2)=0,SMALL(D2:K2,1),"")
0
votes

You need to use an IF:

=IF(D2="","",SMALL(D2:K2,1))
0
votes

=IF(ISBLANK(D2),"",MIN(D2:K2))

0
votes

This is the array formula (Ctrl+Enter to run it), that is needed:

=IF(ISERROR(MATCH(TRUE(),ISBLANK(A1:A4),0)),SMALL(A1:A4,1),"")

The idea is the following:

  • get the first cell in the range, which is not filled out with the array formula=MATCH(TRUE(),ISBLANK(A1:A4),0). It will either return a the position of the empty cell or N/A error, if there is no such cell.
  • put =IF(ISERROR) to check whether there is an empty position in the range, returning an error.
  • unite the formula with =SMALL(A1:A4,1), if there is no empty cell.

Formulas are in German, but I hope it is understandable:

enter image description here

0
votes

You can use the union of two formulas:

The count.if() and the small(), like I did on this case:

=IF(COUNT.IF(Q31:Q38;"")>0;"";SMALL(Q31:Q38)), where Q31:Q38 is my interval.

On count.if we count the number of blank cells (equals to "");

On ìf, we verify if our counter is > 0. If it is, our cell receives the "" value. If it isn't, it receives the smaller data value.