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?

 

[Aladin Akyurek]

I have found a work around..

My problem is that my formulas were in fact delivering numbers as text. My formula was:
=IF(B6=GA$4,R6,)

What I found is that by changing the formula to:
=IF(B6=GA$4,R6,)*1
viola,... I have numbers

Anyone see any drawbacks with this approach?
Thanks to everyone esp Aladin for your help!!

You are welcome.

Apparently R6 is a text-number...

=IF(B6=GA$4,R6+0,0)

That is: +0 is a bit better than *1.

 

[RobLaw]

This is similar to all the above questions but I cannot seem to get the above formulas to work.

I am trying to find the three smallest values in a range. The normal formula i use is:

= Small(List!V5:V117,1)
= Small(List!V5:V117,2)
= Small(List!V5:V117,3)

However I have noticed that some of my data is incomplete and I will be returned a value of -100% or -1. I want to exclude all changes of -100% and move to the next smallest value.

So I need the three smallest values that are not -100%. Can you Help?

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming all the other values are larger than -100% then try ..

=SMALL(List!V5:V117,1+COUNTIF(List!V5:V117,-1))

.. and change the 1 to 2 or 3 as desired.

 

[RobLaw]

Very Nice!

So you counted how many times -1 occured then added one, that is brilliant, and much simpilar then creating a VBA function

Thanks!

 

[michaelwittke]

I have a more interesting dilemma.

Step one would be find find the last instance in a row of 5 that is a negative number
Step two would be to find the first instance in a row of 5 that is a positive number

 

[Aladin Akyurek]

I have a more interesting dilemma.

Step one would be find find the last instance in a row of 5 that is a negative number
Step two would be to find the first instance in a row of 5 that is a positive number

Would you explain what you mean by way of an appropriate example involving both cases?

 

[michaelwittke]

This is in regard to setting up a sheet where I need to know the BE and Payback.

[TABLE="width: 686"]
<colgroup><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Yearly NPV Cash Flow[/TD]
[TD="align: right"]($75,000)[/TD]
[TD="align: right"]$44,643[/TD]
[TD="align: right"]$39,860[/TD]
[TD="align: right"]$35,589[/TD]
[TD="align: right"]$31,776[/TD]
[TD="align: right"]$28,371[/TD]
[/TR]
[TR]
[TD]Overall NPV Cash Flow[/TD]
[TD="align: right"]($75,000)[/TD]
[TD="align: right"]($30,357)[/TD]
[TD="align: right"]$9,503[/TD]
[TD="align: right"]$45,092[/TD]
[TD="align: right"]$76,867[/TD]
[TD="align: right"]$105,239[/TD]
[/TR]
</tbody>[/TABLE]

Normally, I would look for the last instance of a neg figure in a row, in this example, (30,357) deduct 39,860 from 9,503 and so on. To clean the sheet up, I was looking for a function(s) to do this automatically.

thank you

 

[Aladin Akyurek]

This is in regard to setting up a sheet where I need to know the BE and Payback.

[TABLE="width: 686"]
<tbody>[TR]
[TD]Yearly NPV Cash Flow
[/TD]
[TD="align: right"]($75,000)
[/TD]
[TD="align: right"]$44,643
[/TD]
[TD="align: right"]$39,860
[/TD]
[TD="align: right"]$35,589
[/TD]
[TD="align: right"]$31,776
[/TD]
[TD="align: right"]$28,371
[/TD]
[/TR]
[TR]
[TD]Overall NPV Cash Flow
[/TD]
[TD="align: right"]($75,000)
[/TD]
[TD="align: right"]($30,357)
[/TD]
[TD="align: right"]$9,503
[/TD]
[TD="align: right"]$45,092
[/TD]
[TD="align: right"]$76,867
[/TD]
[TD="align: right"]$105,239
[/TD]
[/TR]
</tbody>[/TABLE]

Normally, I would look for the last instance of a neg figure in a row, in this example, (30,357) deduct 39,860 from 9,503 and so on. To clean the sheet up, I was looking for a function(s) to do this automatically.

thank you

The last negative number:

=LOOKUP(9.99999999999999E+307, 1/(B2:G2 < 0), B2:G2)

 

[Wraithed]

I have a very similar question. I have a chart looking at some data and I want to find the lowest point in the chart. I am using: =MIN(IF(A1:BI1,A1:BI1)).

The problem is that in that data range I have values that are = to NA(). This is so the chart does not input those blank values as 0. However my Min function returns NA. Is there a way I can get the MIN function to ignore the cells that are valued as NA?

Thanks!

 

[Aladin Akyurek]

I have a very similar question. I have a chart looking at some data and I want to find the lowest point in the chart. I am using: =MIN(IF(A1:BI1,A1:BI1)).

The problem is that in that data range I have values that are = to NA(). This is so the chart does not input those blank values as 0. However my Min function returns NA. Is there a way I can get the MIN function to ignore the cells that are valued as NA?

Thanks!

Control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(A1:BI1),IF(A1:BI1,A1:BI1)))