Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with the 0 value
Hello,
I need a formula to compare the list of entries in a column and display the minimum of them at the end. For example from D1:D50, each cell has 0 value in each cell based on a formula (=A1+B1+C1), similarly for all the cells from D2 to D50. Based on the entries in the columns A, B and C the total of them is getting displayed in column D. If the entries are 5 (range A1:C5), the sum is getting displayed from D1 to D5 and the other cells D6 to D50 are showing as 0 because of the formula. I am trying to put the formula =MIN(D1:D50) in D51 but the value is showing as 0 instead of the lowest among the five values in D1 to D5. How do I get the minimun value which looks only the entries and displays the minimum ignoring the 0 values in the same column. Thanks in advance Sasikiran |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with the 0 value
an easy way is to put an interim step in column E
=if(d10,d1,"") then calc the min on column E hope this helps. "Sasikiran" wrote: Hello, I need a formula to compare the list of entries in a column and display the minimum of them at the end. For example from D1:D50, each cell has 0 value in each cell based on a formula (=A1+B1+C1), similarly for all the cells from D2 to D50. Based on the entries in the columns A, B and C the total of them is getting displayed in column D. If the entries are 5 (range A1:C5), the sum is getting displayed from D1 to D5 and the other cells D6 to D50 are showing as 0 because of the formula. I am trying to put the formula =MIN(D1:D50) in D51 but the value is showing as 0 instead of the lowest among the five values in D1 to D5. How do I get the minimun value which looks only the entries and displays the minimum ignoring the 0 values in the same column. Thanks in advance Sasikiran |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with the 0 value
Array formula. Enter with Ctrl+Shift+Enter.
This will ignore any values equal to or less than 0. =MIN(IF(D1:D500,D1:D50)) Sasikiran wrote: Hello, I need a formula to compare the list of entries in a column and display the minimum of them at the end. For example from D1:D50, each cell has 0 value in each cell based on a formula (=A1+B1+C1), similarly for all the cells from D2 to D50. Based on the entries in the columns A, B and C the total of them is getting displayed in column D. If the entries are 5 (range A1:C5), the sum is getting displayed from D1 to D5 and the other cells D6 to D50 are showing as 0 because of the formula. I am trying to put the formula =MIN(D1:D50) in D51 but the value is showing as 0 instead of the lowest among the five values in D1 to D5. How do I get the minimun value which looks only the entries and displays the minimum ignoring the 0 values in the same column. Thanks in advance Sasikiran |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |