Conditional format performed on separate rows

[plettieri]

Hi:

I would like highlight the lowest cell value on each of the rows. On the example below I would like to highlight the cell Items 1 - 46, Items 2 - 55, Item 5 - 42, Item 6 - 70.

Do I need to conditional format each row individually?

Easier solution?

MILLER21.XLS
	B	C	D	E	F	G	H	I	J	K	L
8	No.	1	2	3	4	5	6	7	8	9	10
9	1	70	84	86	85	84	72	100	85	66	46
10	2	100	82	74	55	68	82	100	88	92	82
11	3
12
13	5	100	80	75	48	44	66	100	91	70	42
14	6	100	84	78	70	76	74	100	97	82	84
1st qtr

Tks in advance

pll
This message was edited by plettieri on 2002-11-03 18:13

 

[plettieri]

Wow.....

Tks paddy

I have to digest this one....it will take a few hours to disect...I will revert back when I can think more clearly....Every time i post a query, I get more amazed where all this stuff if coming from...keep it up

tks
pll

 

[PaddyD]

unpacked a little:

=ADDRESS(ROW(A10),COLUMN(A10),4,1)=ADDRESS(ROW(A10),MATCH(MIN(10:10),10:10,0),4,1)

ADDRESS(ROW(A10),COLUMN(A10),4,1)

just using the address function to return the address of the cell...

ADDRESS(ROW(A10),MATCH(MIN(10:10),10:10,0),4,1)

using the address function to return the address of the first instance of the minimum value, because:

MATCH(MIN(10:10),10:10,0)

returns the position of the matched value within the specified range. Handily for this problem, the default behaviour is to return the position of the first matched value. (you may need to change references to 10:10 etc(i.e. entire rows) to something more specific).

paddy

 

[Yogi Anand]

Hi plettieri:

In addition to Paddy's valuable contribution, please also try the following Conditional Formating formual:

Formula Is =COLUMN()=MATCH(MIN($A10:$Z10),$A10:$Z10,0)

Please post back if it works for you otherwise explain a little further and let us take it from there.

Regards!
Yogi Anand

 

[plettieri]

Paddy and Yogi,

Thanks so much for your formula contributions to my query. I try to understand all aspects of the formula before I use them in my worksheet so I can try to tweek them if necessary later on. Your explainations were very helpful. It will take some time before I absorb them. Both worked equally as well and provided the exact same results on my tests....although Yogi's formula in length was a bit shorter. I only mention it because in modifying (for my needs) the formula in the edit box of the conditional formatting seemed a bit difficult to modify.....ie everytime I depressed a right or left arrow key while in the edit box a cell location would be entered in that position of the edit box. i was not sure what was doing that. I don't know if that was abnormal or not as I rarely used conditional formatting.

Anyway, thanks again to both and close out this thread.

pll

 

[plettieri]

For Yogi:

In your last post you were kind to provide me a formula

Formula Is =COLUMN()=MATCH(MIN($A10:$Z10),$A10:$Z10,0)

Which works fine with the starting postition in cell a10....I changed my starting postition to c10 and ending position to AE10 and have changed the formula in the conditional format section to =COLUMN()=MATCH(MIN($c10:$ae10),$c10:$ae10,0) ...however this does not produces the same results as the formula you provided. This formula seems to identify numbers that are not the lowest in that range..seems to be shifted ....I think?

Just to retate my objective: to conditional format a range of cells (c10:ae10)on one line that identifies the first lowest number found in that range.


Because I changed starting locations...should there be something more than just adjusting columns?

Any ideas where I am going astray?



tks for looking.

pll
This message was edited by plettieri on 2002-11-04 23:37

 

[Yogi Anand]

Hi plettieri:

Just change the formula from
=COLUMN()=MATCH(MIN($c10:$ae10),$c10:$ae10,0)
to
=COLUMN()=MATCH(MIN($c10:$ae10),$a10:$ae10,0)

the change is necessary because we want to measure the column number counting from column A being 1.

Regards!
Yogi

 

[plettieri]

Yogi Anand:

Thanks again so much..your formula works now as i had expected..as soon as i supplied all the info needed....i will try and do a better job next time and getting all the info at the outset of the query.

I pulled a little bit of hair out of my head last night and today trying ever combination to adjust to fit my needs from the initial formula you proposed.....I did come up with something that i think works the same way as your update, but would like your feedback on it...what i did was take your inital formula

=COLUMN()=MATCH(MIN($a10:$Z10),$a10:$Z10,0) and modified it to start 2 columns to the right...(from A to C)...so I tried adding +2 to the tail of the formula so it looks like this
=COLUMN()=MATCH(MIN($c10:$Z10),$c10:$Z10,0)+2

It seems to work....but yours, now that i see it, is more logical and rational...

This relative and absolute positioning is a task unto itself to absorb.

tks again
pll

 

[Yogi Anand]

Hi Good Job plettieri:

The addition of 2 to the column number will work -- however, as you said, you will have to keep adjusting it if you changed your starting cell of the range, and by simply changing the match range to start with column A regardless, you are all set no matter what your starting cell for the range where you want to apply CF.

Regards!
Yogi Anand