Highlight nth smallest cells greater than target cell

[Travis Frank]

I am looking to highlight the 10 smallest cells in a single column range that are greater than the value that is in a target cell. I also need to be able to change the number of cells being highlighted if needed. Can anyone help with the formula needed to make this happen in Conditional Formatting??

 

[GraH - Guido]

Travis Frank,

Welcome to Chandoo's wonders.
Did you read the forum rules? Please do.

To allow us to help you, help us with posting a sample file containing some relevant data and if possible a manual made solution.
So far the answer to the question would be: "Yes, we can help you."

 

[Travis Frank]

My file shows the current stock on hand at the start of the week, 3225. It also shows the average weekly usage, 128.0 and the inventory at the end of the week. The product has a 10 week Lead Time. I am looking to highlight the 10 smallest ending values greater than the weekly usage (C3).This would signal that either an order needs to be placed before the highlighted section or weekly demand needs to reduce

 

[r2c2]

@Travis Frank Interesting question. You can use Conditional Formatting > Between rule with formulas to check this.

See attached file.

Here is how it works.

We want to highlight 10 cells that are between smallest positive stock & 9 prior weeks.

=SMALL(IF($C$5:$C$69>$C$3,$C$5:$C$69),10)

gives you 10th smallest item

=MIN(IF($C$5:$C$69>$C$3,$C$5:$C$69))

gives you 1st smallest item (ie minimum)

We highlight everything in between.

Hope that helps.