Comparing Data

[Guest]

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

 

[Guest]

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)),INDIRECT(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)),INDIRECT(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

 

[Guest]

Many thanks for you're assistance.