Using MIN function in column with blanks

[oldpup223]

I have a scoring sheet that lists scores in a column but never know how many names will be entered and want to find the minimum score in a column with several blanks above the formula

 

[Yongle]

Which version of Excel are you using

This formula is simplest but it is not available in earlier versions
=MINIFS(C:C,C:C,"<>")

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td]

100​

[/td][td][/td][td=bgcolor:#FFFF00]

10​

[/td][td] =MINIFS(C:C,C:C,"<>")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td][/td][td]

25​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td]

50​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td]

75​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td]

25​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td=bgcolor:#FFFF00]

10​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td]

100​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td]

1100​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td]

27​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td][/td][td]

100​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td]

59​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td]

77​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[Yongle]

From your post, I assumed that the you had tried the obvious
=MIN(C:C)

That works for me and ignore blanks. Does it not work for you ?

 

[oldpup223]

Thank you for the reply. I have several rows with data (E through I) and in the row end in column (L) I have
a +MIN(E5:I5) and that works fine then at the bottom of column (L) I have another =MIN(L5:L105) and that will not work as there are many blanks between row 5 and 105 so either it woll not work because of the blank rows or you cannot use the min statement on a column of generated numbers by the min statement at the end of the rows. thank you for any help you may wish to give.

 

[oldpup223]

Thank you for the reply. I have several rows with data (E through I) and in the row end in column (L) I have
a +MIN(E5:I5) and that works fine then at the bottom of column (L) I have another =MIN(L5:L105) and that will not work as there are many blanks between row 5 and 105 so either it woll not work because of the blank rows or you cannot use the min statement on a column of generated numbers by the min statement at the end of the rows. thank you for any help you may wish to give

 

[Yongle]

What is a in
a +MIN(E5:I5)

 

[oldpup223]

Thank you for the reply. I have several rows with data (E through I) and in the row end in column (L) I have

=MIN(E5:I5) and that works fine then at the bottom of column (L) I have another =MIN(L5:L105) and that will not work as there are many blanks between row 5 and 105 so either it will not work because of the blank rows or you cannot use the min statement on a column of generated numbers by the min statement at the end of the rows. thank you for any help you may wish to give

---End Quote---

 

[Fluff]

In what way is you formula not working?
Also are the blanks actually blank or do they show 0 as there is nothing in that row?

 

[oldpup223]

yes the cells are blank and after using =min(l5:l105) in cell l106 I just get a blank cell but the formula is still there

 

[Fluff]

Are you sure that your blanks are actually blank? Rather than contain a 0 that is hidden by formatting, or because you have unchecked "Show a zero in cells that have zero value"?