Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Benefits many people - Question | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Follow on from simple question 12/8/2004 | Excel Discussion (Misc queries) |