Histogram

[Nestor]

Hi,
Is there any way in which excel can recognise two numbers as a range? I
would like to create a kind of histogram which can describe how many times a
point along this range has been crossed over a specific time frame...

For example:

High Low
Day1 300 290
Day2 310 295
Day3 305 290
Day4 300 285

Range Day1 Day2 Day3 Day4 Count
280 0
285 x 1
290 x x x 3
295 x x x x 4
300 x x x x 4
305 x x 2
310 x 1
315 0

 

[T. Valko]

One way...

This data in the range A2:C5 -

Day1...300...290
Day2...310...295
Day3...305...290
Day4...300...285

Your matrix table in the range...

Column headers: A8:F8
Row headers: A9:A16

Enter this formula in B9:

=IF($A9=MEDIAN(INDEX($B$2:$C$5,MATCH(B$8,$A$2:$A$5,0),0),$A9),"x","")

Copy across to E9 then down to B16:E16

For the count enter this formula in F9:

=COUNTIF(B9:E9,"x")

Copy down to F16.

 

[Nestor]

Thank you very much T. Valko, that was very helpful. I have one more question
for you:

On Day4 (or the last day), if I have a close number at 298, for example, how
can I indicate this on the count? Such as highlighting the count number red.

Example:
..........High..Low..Close
Day1...300...290...295
Day2...310...295...303
Day3...305...290...302
Day4...300...285...298

 

[T. Valko]

I'm not sure I understand the relationship.

Can you post several more examples and tell me which cell should be
highlighted? For example:

If the 298 was:

280, then highlight ___
288, then highlight ___
320, then highlight ___
302, then highlight ___