Two highest values in column?

[Mike Slattery]

I have tried to utilize the following formula but it is returning zeros?

=COUNTIF(CH3,(RANK(CH3,CH3:CH62,0)<3))

I just need to graph the two highest values in a column of stock prices to produce a resistance line.

Thanks for your assistance,

Michael

 

[JoeMo]

Can you use the LARGE function?

 

[Scott Huish]

Also, if there are duplicate values, do you want to only consider unique values as the top 2 or the duplicate values as the top 2?

In other words, if you had the following values: 5,5,4,3,2

Do you consider the two highest values to be 5 & 5 or 5 & 4?

 

[Mike Slattery]

Thank you both! It does not matter if there are duplicate values and "Large" worked very well for the resistance trend line. Thank you for that. It does not work for the support line where I need to determine the two smallest values in the column representing the Low of the day, for the range of days for that stock. I have tried "SMALL", but there are a lot of zeros in this column making that solution unworkable. I need to select the second lowest value from the column that is not zero and then (in another formula) select all cells that have a value <= that value. Part of my problem here is maintaining the horizontal positions of the occurrence of these highs or Lows with there dates, so that they will plot correctly in the chart.

Thanks for you assistance,

Michael

 

[JoeMo]

Thank you both! It does not matter if there are duplicate values and "Large" worked very well for the resistance trend line. Thank you for that. It does not work for the support line where I need to determine the two smallest values in the column representing the Low of the day, for the range of days for that stock. I have tried "SMALL", but there are a lot of zeros in this column making that solution unworkable. I need to select the second lowest value from the column that is not zero and then (in another formula) select all cells that have a value <= that value. Part of my problem here is maintaining the horizontal positions of the occurrence of these highs or Lows with there dates, so that they will plot correctly in the chart.

Thanks for you assistance,

Michael

You can beat the zeroes problem with an array formula like this. Say you want the two smallest non-zero values in A5:A14. then:

=SMALL(IF(A5:A14>0,A5:A14),1) returns the smallest value

and =SMALL(IF(A5:A14>0,A5:A14),2) the second smallest

NOTE: the formulas must be confirmed by pressing ctrl+shift+enter not just enter. You will know if you have done that correctly by the appearance (in the formula bar) of curly braces around the entire formula.

You could replace the 1 in the first formula with Row(A1) if you want to drag it down to get the second smallest in the cell immediately beneath the one that returns the smallest.

 

[Mike Slattery]

That's the ticket ;√)

Thanks, Michael

 

[JoeMo]

That's the ticket ;√)

Thanks, Michael

You are welcome - thanks for the reply.