Comparing Data
 

I have the following data.

A B C D E
1 0.45 1.5 1.8 24.5 19
2 0.40 1.5 1.4 28 37
3 0.15 1.5 1.0 25 34
4 0.40 1.5 1.4 28 37
5 0.40 1.5 1.5 37 47
6 0.45 1.5 2.0 35 48
7 0.45 1.5 2.0 35 48
8 0.35 1.5 1.0 26 33
9 0.05 1.5 0.5 26 40
10 0.44 1.7 1.06 26.37 35.24
11 0.438 1.03 1.6 35.21 44.78
12 0.088 0.09 0.07 21.87 54.79

I would like to select 1 row from rows 10, 11 or 12.

I would then select 2 rows from rows 1 to row 9.

From the three rows selected I would like the cells in row 11 for example to
be compared with row 3 and row 5.

If the value in Cell 11A is equal to or greater than the lower value in Cell
3A and 5A, cells 3A and 5A go green.

If the value in Cell 11A is equal to or less than the upper value in Cell 3A
and 5A, cells 3A and 5A go green.

If the value in Cell 11A is below the lower value in Cell 3A and 5A, cells
3A and 5A go red.

If the value in Cell 11A is above the upper value in Cell 3A and 5A, cells
3A and 5A go red.

My experience with Excel is very limited so a step by step approach would be
appreciated.
Many thanks

Comparing Data
 

I saw that your post had not been answered so I thought I'd give it a try.
I'm not an expert but try this:

insert or use a blank row and type this into the first cell:
=CHAR(64+COLUMN())
then copy across as far as you have data to compare....what that will do is
put letters that represents column headers we will use this in our formula (I
used row 13 for my test), you can hide this row when done.

Next select 3 cells that will represent your row selections....for my test I
selected H2 and H3 for the 2 rows to select between row 1 and row 9, and H5
for the selection of row between row 10 and 12.

Next select your data (row 1 thru 9) using your example and go to
Format/Conditional formatting, for the first condition select "Formula is"
from the drop down menu and type this into the formula box:

=AND(OR(ROW()=$H$3,ROW()=$H$2),INDIRECT(A$13&$H$5) <=MAX(INDIRECT(A$13&$H$2),INDIRECT(A$13&$H$3)),IND IRECT(A$13&$H$5)=MIN(INDIRECT(A$13&$H$2),INDIRECT (A$13&$H$3)))

click on Format and choose a green background

next click on "ADD", again select "Formula is" from the drop down menu and
type:

=AND(OR(ROW()=$H$3,ROW()=$H$2),OR(INDIRECT(A$13&$H $5)MAX(INDIRECT("A"&$H$2),INDIRECT("A"&$H$3)),IND IRECT(A$13&$H$5)<MIN(INDIRECT(A$13&$H$2),INDIRECT( A$13&$H$3))))

click on "Fromat" and select a red background

A$13 is the row I used to enter the column letters using the first formula I
gave you.
Of course your setup will be different so ajust to suit.

Hope this helps!

Jean-Guy



"zephyr" wrote:

I have the following data.

A B C D E
1 0.45 1.5 1.8 24.5 19
2 0.40 1.5 1.4 28 37
3 0.15 1.5 1.0 25 34
4 0.40 1.5 1.4 28 37
5 0.40 1.5 1.5 37 47
6 0.45 1.5 2.0 35 48
7 0.45 1.5 2.0 35 48
8 0.35 1.5 1.0 26 33
9 0.05 1.5 0.5 26 40
10 0.44 1.7 1.06 26.37 35.24
11 0.438 1.03 1.6 35.21 44.78
12 0.088 0.09 0.07 21.87 54.79

I would like to select 1 row from rows 10, 11 or 12.

I would then select 2 rows from rows 1 to row 9.

From the three rows selected I would like the cells in row 11 for example to
be compared with row 3 and row 5.

If the value in Cell 11A is equal to or greater than the lower value in Cell
3A and 5A, cells 3A and 5A go green.

If the value in Cell 11A is equal to or less than the upper value in Cell 3A
and 5A, cells 3A and 5A go green.

If the value in Cell 11A is below the lower value in Cell 3A and 5A, cells
3A and 5A go red.

If the value in Cell 11A is above the upper value in Cell 3A and 5A, cells
3A and 5A go red.

My experience with Excel is very limited so a step by step approach would be
appreciated.
Many thanks

Comparing Data
 

Many thanks for you're assistance.

"zephyr" wrote:

I have the following data.

A B C D E
1 0.45 1.5 1.8 24.5 19
2 0.40 1.5 1.4 28 37
3 0.15 1.5 1.0 25 34
4 0.40 1.5 1.4 28 37
5 0.40 1.5 1.5 37 47
6 0.45 1.5 2.0 35 48
7 0.45 1.5 2.0 35 48
8 0.35 1.5 1.0 26 33
9 0.05 1.5 0.5 26 40
10 0.44 1.7 1.06 26.37 35.24
11 0.438 1.03 1.6 35.21 44.78
12 0.088 0.09 0.07 21.87 54.79

I would like to select 1 row from rows 10, 11 or 12.

I would then select 2 rows from rows 1 to row 9.

From the three rows selected I would like the cells in row 11 for example to
be compared with row 3 and row 5.

If the value in Cell 11A is equal to or greater than the lower value in Cell
3A and 5A, cells 3A and 5A go green.

If the value in Cell 11A is equal to or less than the upper value in Cell 3A
and 5A, cells 3A and 5A go green.

If the value in Cell 11A is below the lower value in Cell 3A and 5A, cells
3A and 5A go red.

If the value in Cell 11A is above the upper value in Cell 3A and 5A, cells
3A and 5A go red.

My experience with Excel is very limited so a step by step approach would be
appreciated.
Many thanks