Find lowest value in range excluding 0

[karini4]

I have been trying to enter a formula to find the lowest value in a range, but I want to exclude 0's. I haven't been able to figure it out. Any suggestions?

 

[lukeMV]

That works perfectly.

Let's throw in some more complexity. I have the logic, but think I am missing something in syntax.


I am adding a third column. This column is numbers. I want to take the highest number and give the "value" column a number (ie: "3"), but if there are duplicates in this new column, I want to find the value closest to zero (negative or positive) from the original first column.

Ie

| A | B | C |
------------------
| 10 | -1 | 3 |
| 9 | 2 | |
| 7 | 5 | |
| 10 | 1 | 3 |
| 8 | 8 | |
| 5 | 10 | |

Make sense?

I figured this would do it, but must be missing something:

=IF(A3>0,(IF(A3=LARGE(A3:A32, 1),(IF(B3=MIN(B$3:B$32),10,"0")),"0")),"0")

 

[lukeMV]

That works perfectly.

Let's throw in some more complexity. I have the logic, but think I am missing something in syntax.


I am adding a third column. This column is numbers. I want to take the highest number and give the "value" column a number (ie: "3"), but if there are duplicates in this new column, I want to find the value closest to zero (negative or positive) from the original first column.

Ie

| A | B | C |
------------------
| 10 | -1 | 3 |
| 9 | 2 | |
| 7 | 5 | |
| 10 | 1 | 3 |
| 8 | 8 | |
| 5 | 10 | |

Make sense?

I figured this would do it, but must be missing something:

=IF(A3>0,(IF(A3=LARGE(A3:A32, 1),(IF(B3=MIN(B$3:B$32),10,"0")),"0")),"0")

I should mention, if column A has only one highest value, it's row in column C can get the "3" rather than having to check with the value in B. Make sense?

 

[Peter_SSs]

Make sense?

Not enough.

Please give 2 or 3 more sets of values, including the expected results and explain why the result is the expected one.

It would also make things easier if your sample data was more easily able to be copied to a worksheet for testing. You might investigate these methods for doing so.

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste

You can test in the Test Here forum.

 

[lukeMV]

Let's see if this makes more sense. The final value "FINAL" is based primarily on column A where the highest number is awarded the final value. (example 1).

When there are two values in column A that are equal (example 2) then the "tie breaker" is determined by the second column---where the two largest values of A then look at column B to see, of those two (or more) numbers which is closest to 0. The closest value is then given the FINAL points.

does that clear it up at all?

<table style="table-layout: fixed;" border="0" cellpadding="0" cellspacing="0" width="225"><col width="75" span="3"><tbody><tr height="13"><td class="xl25" height="13" width="75"> A</td><td class="xl25" width="75"> B</td><td class="xl24" width="75"> FINAL</td></tr><tr height="13"><td align="right" height="13">7</td><td align="right">-5</td><td>
</td></tr><tr height="13"><td align="right" height="13">4</td><td align="right">-20</td><td>
</td></tr><tr height="13"><td align="right" height="13">11</td><td align="right">0</td><td align="right">3</td></tr><tr height="13"><td align="right" height="13">7</td><td align="right">0</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-14</td><td>
</td></tr><tr height="13"><td align="right" height="13">6</td><td align="right">-11</td><td>
</td></tr><tr height="13"><td align="right" height="13">4</td><td align="right">-14</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-10</td><td>
</td></tr><tr height="13"><td align="right" height="13">10</td><td align="right">0</td><td>
</td></tr><tr height="13"><td align="right" height="13">10</td><td align="right">-7</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-6</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-13</td><td>
</td></tr><tr height="13"><td height="13">
</td><td>
</td><td>
</td></tr><tr height="13"><td height="13">EXAMPLE 2</td><td>
</td><td>
</td></tr><tr height="13"><td height="13">
</td><td>
</td><td>
</td></tr><tr height="13"><td class="xl25" height="13"> A
</td><td class="xl25"> B</td><td class="xl24"> FINAL</td></tr><tr height="13"><td align="right" height="13">7</td><td align="right">-5</td><td>
</td></tr><tr height="13"><td align="right" height="13">4</td><td align="right">-20</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">0</td><td>
</td></tr><tr height="13"><td align="right" height="13">7</td><td align="right">0</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-14</td><td>
</td></tr><tr height="13"><td align="right" height="13">6</td><td align="right">-11</td><td>
</td></tr><tr height="13"><td align="right" height="13">4</td><td align="right">-14</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-10</td><td>
</td></tr><tr height="13"><td align="right" height="13">10</td><td align="right">2</td><td align="right">3</td></tr><tr height="13"><td align="right" height="13">10</td><td align="right">-7</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-6</td><td>
</td></tr><tr height="13"><td align="right" height="13">8</td><td align="right">-13</td><td>
</td></tr></tbody></table>

 

[Peter_SSs]

What is to happen if column B is a tie as well?

 

[lukeMV]

If column B were also a tie, then both would get the same FINAL result.

 

[Peter_SSs]

Try this array formula.

Excel Workbook
	A	B	C
1	7	-5
2	4	-20
3	11	0	3
4	7	0
5	8	-14
6	6	-11
7	4	-14
8	8	-10
9	10	0
10	10	-7
11	8	-6
12	8	-13
Closest to 0

 

[lukeMV]

Brilliant! I've spent hours trying to figure this out and you've solved it in a matter of minutes. Thank you!

 

[JWahlin]

Hi,

I'm looking for a solution to the following.

I have a table with persons and a score, same person have many records (scores) and i want to find the lowest score per person in the table.

Ive been trying to type something like =MIN(C1:C60);IF(CG55=person1)
where cell CG55 would have the persons name in it. But this obviously wont work.

Any ideas?

table looks something like this
Name date score
person1 xxx 10
person2 xxx 12
person1 xxx 12
person3 xxx 11
person2 xxx 14
etc...



Thanks
JW

 

[bosco_yip]

Ive been trying to type something like =MIN(C1:C60);IF(CG55=person1)

Try, array formula :

{=MIN(IF(A1:A60=CG55,C1:C60))}

Regards