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.

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.

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.

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.

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.