Prev: Match - not sure how to use this function
Next: Help with SUMPRODUCT & converting date to value
From: vmohan1978 on 5 May 2010 07:34
Dear All,
I am preparing a compartive statement i want to highlight the max and min a
group of cells.
quote-1 quote-2 quote-3
100 150 200

like this upto quote-6
I want to highlight the number max of 6 as red and min of 6 as yellow.

Please do the needful .

From: Jacob Skaria on 5 May 2010 07:58
1. Select the cell/Range (say A2:F2). Please note that the cell reference A2
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=A2=MAX($A$2:$F$2)
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

For Condition2 replace MAX() with MIN()

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"vmohan1978" wrote:

> Dear All,
> I am preparing a compartive statement i want to highlight the max and min a
> group of cells.
> quote-1 quote-2 quote-3
> 100 150 200
>
> like this upto quote-6
> I want to highlight the number max of 6 as red and min of 6 as yellow.
>
> Please do the needful .
>
From: Russell on 5 May 2010 11:58
Thanks Steve. I am going to give it a shot. The more I read the more I think
that I am asking too much of Excel. At least the 2003 version that I am using.

Have a good day.

Russell

"Steve Dunn" wrote:

> Not sure what you mean by "within the previous 365 days".
>
> if you're referring to 365 days prior to the current date
>
> =(B2<>100)*(A2>TODAY()-365)
>
> or if you mean 365 days prior to the value's adjacent date (with sorted
> dates)
>
> =(B2<>100)*SUMPRODUCT(($A$2:$A1>$A2-365)*($B$2:$B1=B2))
>
> or (if dates are not sorted)
>
> =(B2<>100)*SUMPRODUCT(($A$2:$A$1000>$A2-365)*($B$2:$B$1000=B2))
>
>
> HTH
> Steve D.
>
> "Russell" <Russell(a)discussions.microsoft.com> wrote in message
> news:7AE77683-0813-4FF9-8FFD-96A2F449996E(a)microsoft.com...
> >I want to be able to search for two things in a column. The first is a
> >value
> > of less than or greater than $100. (OK so that is really two things I
> > guess).
> > The second is to be able to see if that amount is within the previous 365
> > days. There is another column for the date. I can get part to work but am
> > having trouble with the date. Any ideas would be greatly appreciated.
>
 | 
Pages: 1
Prev: Match - not sure how to use this function
Next: Help with SUMPRODUCT & converting date to value