Follow-Up (Clarification) to MIN question
 

Hello all Excel Guru's:

This is a follow-up post that I made about a week ago. I had gotten
several responses to my question of MAX and MIN.

My question was, using the table below and using the following
formula:
=MIN(C1:C10) located in Cell C11, the answer should be 100
=MAX(C1:C10) located in Cell C12, the answer is 450

In column C, I have the following formulas:
=sum(A1+B1) and this is copied down to C10

A B C
1 100 100 200
2 150 200 350
3 200 250 450
4 50 150 200
5 50 50 100
6 125 275 400
7 100 125 225
8
9
10
11 The Min =
12 The Max = 450

The issue that I am having is with the MIN. Since there is no data in
C8:C10 because A8:B10 is empty, the MIN shows up zero (0), nothing in
C11 for MIN. but if I change the range from C1:C10 to a new range of
C1:C7, I get the correct answer of 100. Everyone stated that MIN
ignores blank cells, but C8:C10 is not really empty or blank, there is
a formula in each of thoses cells.

so my question becomes I do i get a MIN for range C1:C10.
Any and all help in this matter is greatly appreciated.

Follow-Up (Clarification) to MIN question
 

One way is to change your formula in Column C to test for blank cells in Col
A and B
IF(AND(A1="",B1=""),"",A1+B1)

OR, modify your MIN function to filter out the zeros:

MIN(IF(C1:C10=0,"",C1:C10))

This is an array function, so confirm w/Cntrl+Shift+Enter instead of just
Enter.

"Odawg" wrote:

Hello all Excel Guru's:

This is a follow-up post that I made about a week ago. I had gotten
several responses to my question of MAX and MIN.

My question was, using the table below and using the following
formula:
=MIN(C1:C10) located in Cell C11, the answer should be 100
=MAX(C1:C10) located in Cell C12, the answer is 450

In column C, I have the following formulas:
=sum(A1+B1) and this is copied down to C10

A B C
1 100 100 200
2 150 200 350
3 200 250 450
4 50 150 200
5 50 50 100
6 125 275 400
7 100 125 225
8
9
10
11 The Min =
12 The Max = 450

The issue that I am having is with the MIN. Since there is no data in
C8:C10 because A8:B10 is empty, the MIN shows up zero (0), nothing in
C11 for MIN. but if I change the range from C1:C10 to a new range of
C1:C7, I get the correct answer of 100. Everyone stated that MIN
ignores blank cells, but C8:C10 is not really empty or blank, there is
a formula in each of thoses cells.

so my question becomes I do i get a MIN for range C1:C10.
Any and all help in this matter is greatly appreciated.

Follow-Up (Clarification) to MIN question
 

Instead of SUM(A1+B1), use
=IF(COUNT(A1:B1)0,A1+B1,"")
MIN() will ignore the null text strings.

FYI =SUM(A1+B1) is redundant. You can use the shorter form =A1+B1.
SUM() exists so that you can add a whole range, such as =SUM(A1:G1)
without having to list all the cells individually.

Jerry

Odawg wrote:

Hello all Excel Guru's:

This is a follow-up post that I made about a week ago. I had gotten
several responses to my question of MAX and MIN.

My question was, using the table below and using the following
formula:
=MIN(C1:C10) located in Cell C11, the answer should be 100
=MAX(C1:C10) located in Cell C12, the answer is 450

In column C, I have the following formulas:
=sum(A1+B1) and this is copied down to C10

A B C
1 100 100 200
2 150 200 350
3 200 250 450
4 50 150 200
5 50 50 100
6 125 275 400
7 100 125 225
8
9
10
11 The Min =
12 The Max = 450

The issue that I am having is with the MIN. Since there is no data in
C8:C10 because A8:B10 is empty, the MIN shows up zero (0), nothing in
C11 for MIN. but if I change the range from C1:C10 to a new range of
C1:C7, I get the correct answer of 100. Everyone stated that MIN
ignores blank cells, but C8:C10 is not really empty or blank, there is
a formula in each of thoses cells.

so my question becomes I do i get a MIN for range C1:C10.
Any and all help in this matter is greatly appreciated.

Follow-Up (Clarification) to MIN question
 

Or this entered normally

=SMALL(B1:B10,COUNTIF(B1:B10,0)+1)

as long as values are positive

--
Regards,

Peo Sjoblom

(No private emails please)


"JMB" wrote in message
...
One way is to change your formula in Column C to test for blank cells in
Col
A and B
IF(AND(A1="",B1=""),"",A1+B1)

OR, modify your MIN function to filter out the zeros:

MIN(IF(C1:C10=0,"",C1:C10))

This is an array function, so confirm w/Cntrl+Shift+Enter instead of just
Enter.

"Odawg" wrote:

Hello all Excel Guru's:

This is a follow-up post that I made about a week ago. I had gotten
several responses to my question of MAX and MIN.

My question was, using the table below and using the following
formula:
=MIN(C1:C10) located in Cell C11, the answer should be 100
=MAX(C1:C10) located in Cell C12, the answer is 450

In column C, I have the following formulas:
=sum(A1+B1) and this is copied down to C10

A B C
1 100 100 200
2 150 200 350
3 200 250 450
4 50 150 200
5 50 50 100
6 125 275 400
7 100 125 225
8
9
10
11 The Min =
12 The Max = 450

The issue that I am having is with the MIN. Since there is no data in
C8:C10 because A8:B10 is empty, the MIN shows up zero (0), nothing in
C11 for MIN. but if I change the range from C1:C10 to a new range of
C1:C7, I get the correct answer of 100. Everyone stated that MIN
ignores blank cells, but C8:C10 is not really empty or blank, there is
a formula in each of thoses cells.

so my question becomes I do i get a MIN for range C1:C10.
Any and all help in this matter is greatly appreciated.

Follow-Up (Clarification) to MIN question
 

Wow, thanks all, all of your examples provided worked perfectly.

All of your responses are greatly appreciated...

Argus


On Thu, 20 Oct 2005 02:20:16 GMT, Odawg
wrote:

Hello all Excel Guru's:

This is a follow-up post that I made about a week ago. I had gotten
several responses to my question of MAX and MIN.

My question was, using the table below and using the following
formula:
=MIN(C1:C10) located in Cell C11, the answer should be 100
=MAX(C1:C10) located in Cell C12, the answer is 450

In column C, I have the following formulas:
=sum(A1+B1) and this is copied down to C10

A B C
1 100 100 200
2 150 200 350
3 200 250 450
4 50 150 200
5 50 50 100
6 125 275 400
7 100 125 225
8
9
10
11 The Min =
12 The Max = 450

The issue that I am having is with the MIN. Since there is no data in
C8:C10 because A8:B10 is empty, the MIN shows up zero (0), nothing in
C11 for MIN. but if I change the range from C1:C10 to a new range of
C1:C7, I get the correct answer of 100. Everyone stated that MIN
ignores blank cells, but C8:C10 is not really empty or blank, there is
a formula in each of thoses cells.

so my question becomes I do i get a MIN for range C1:C10.
Any and all help in this matter is greatly appreciated.