Fornula for Highest Quantity

[RUPESHRH]

Dear Experts

Column "B" indicates lot size and column "C" indicates quantity. Column "B47" is the midpoint which is dynamic. Which sometimes keeps moving up and down.
i have requited highest 1 and second highest quantity in between range "C47" to "C95".
and also required the highest 1 and Second highest in the Range "C4" to "C46".
please note that the Mid Point moves up and downs every 5 minutes interval so Range should be change according to the mid point.
Require formula in "E5" and "E6" columns.
Require formula in "E8" and "E9" columns.

Thanks in Advance

[Glenn Kennedy]

I don't know where you got 21575 from...

=LARGE(INDEX(C:C,1+MATCH(10^100,C:C)/2):INDEX(C:C,MATCH(10^100,C:C)),ROWS(F$5:F5))

copied down, and

=LARGE($C$4:INDEX(C:C,MATCH(10^100,C:C)/2),ROWS(F$8:F8))

copied down.

[HansDouwe]

Why is in your example the midpoint between C46 and C47?
C4 to C46 are 43 elements, C47 to C95 are 49 elements.

So the second half has 6 elements more as the first half?

[TMS]

@Hans: possibly a miscalculation. There are 92 cells in the range B4:B95. Maybe the OP forgot to add on the rows before the range begins. Maybe it should be rows 49 to 50.

The average is 2375 which is between rows 49 and 50.