Conditionally highlight cells or ...

:confused:  Hi, Could anyone help me with this problem? Here is th
table:

Apple 	Banana	Orange 	Pear
15	45	20	335
85	65	44	77
11	787	88	936


I want to either  to be able to 1)use a function to return the name o
the fruit containing the lowest level in each raw, so for first row i
would return "Apple", sedond row would return "Orange", etc.  Can an
of you help me writing this function? 2) or if possible, i'd like t
highlight the cell in each row that contains the lowest value, so fo
first row, cell containing 15 will be highlighted. Can anybody help m
with that? Thanks very much

--
linyino
-----------------------------------------------------------------------
linyinow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2834
View this thread: http://www.excelforum.com/showthread.php?threadid=47928

0
10/25/2005 10:00:22 PM
excel 39879 articles. 2 followers. Follow

2 Replies
557 Views

Similar Articles

[PageSpeed] 35

With your data in A2:Dxxx (headers in A1:D1), put this E2:

=INDEX($A$1:$D$1,MATCH(MIN(A2:D2),A2:D2,0))
and drag down.

Select your range A2:Dxxx and with A2 the activecell:
Format|conditional formatting
Formula is:
      =A2=MIN($A2:$D2)

And use a nice pattern.



linyinow wrote:
> 
> :confused:  Hi, Could anyone help me with this problem? Here is the
> table:
> 
> Apple   Banana  Orange  Pear
> 15      45      20      335
> 85      65      44      77
> 11      787     88      936
> 
> I want to either  to be able to 1)use a function to return the name of
> the fruit containing the lowest level in each raw, so for first row it
> would return "Apple", sedond row would return "Orange", etc.  Can any
> of you help me writing this function? 2) or if possible, i'd like to
> highlight the cell in each row that contains the lowest value, so for
> first row, cell containing 15 will be highlighted. Can anybody help me
> with that? Thanks very much!
> 
> --
> linyinow
> ------------------------------------------------------------------------
> linyinow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28345
> View this thread: http://www.excelforum.com/showthread.php?threadid=479282

-- 

Dave Peterson
0
petersod (12004)
10/25/2005 10:24:26 PM
"linyinow" <linyinow.1xhaeb_1130277902.4835@excelforum-nospam.com> wrote in 
message news:linyinow.1xhaeb_1130277902.4835@excelforum-nospam.com...
>

>
> Apple Banana Orange Pear
> 15 45 20 335
> 85 65 44 77
> 11 787 88 936
>
>
> I want to either  to be able to 1)use a function to return the name of
> the fruit containing the lowest level in each raw, so for first row it
> would return "Apple", sedond row would return "Orange", etc.  Can any
> of you help me writing this function? 2) or if possible, i'd like to
> highlight the cell in each row that contains the lowest value, so for
> first row, cell containing 15 will be highlighted. Can anybody help me
> with that? Thanks very much!
>

If you put your table in range A1:D4, than, for returning a name of the 
fruit with lowest level in each row use formula:

=INDEX($A$1:$D$4,1,MATCH(MIN(A2:D2),A2:D2,0)),

where $A$1:$D$4 is the range of table with fruit, 1 is row number from which 
index function returns reference, and MATCH returns column which have min. 
value in this row.

To higlight minimal value in each row, select rows 2:4, and go to 
Format->Conditional Formating, and in dialog select "Cell value is equal to" 
and in reference field put formula:

=MIN($A2:$D2),

and choose desired font, border or pattern. 


0
10/26/2005 12:02:37 AM
Reply:

Similar Artilces:

Need to return "Blank" cell but NOT NA()
For my chart, I need to return the equivalent of a truly "blank" cell. The NA() function causes the graph to interpolate the missing point, whereas a blank cell causes the graph to plot nothing. I need to plot nothing. I'm using layered lines, one dashed and one solid, so that I can highlight interpolated points with a dashed style. Currently, I use a white line to mask the solid line that goes to "0" when the driving formula returns "" for the solid line. This works in 90% of situations, but there are some instances that are too complex (too man...

How to change cell color based on content
Hi, hope someone can help me. I don't use Excel much, but I'm creating a spreadsheet that keeps track of ticket sales for a charity I volunteer for. Anyway, what I want to do is to make the cells stand out for those that still owe money, either because a check is forthcoming or because they ordered by credit card. I want the credit card orders to show up with a different color (eg YELLOW) than those who are fully paid. I want the forthcoming check orders to show up in a different color (eg RED). So I have a column with payment type that is either Cash, Check, or Credit Card....

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Apply a mutiple to a range of cells?
I have a range of cells spanning multiple columns and rows. I would like, in my fantasy, to just hightlight all of them and say "multiply by x" where x is an integer. Now, I know I can't do that because my microphone doesn't work, so, other then a macro, is there a way to do it? Thanks, -Jim In an unused cell enter the numnber that you want to multiply by - it can be an integer or a floating point number - then highlight the range to you want to mutiply and then right-click and select Paste Special > Multiply > Ok Don't forget to say "Multipy by x"...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Format cells #7
I have Excel 2000, Win ME. Recently the pulldown command to format cells does not always respond - ie, no box appears, so I can't apply borders or control number format. The shortcut approach doesn't work either. I've tried Help..Detect and Repair but the fault remains. Any suggestions welcome. ...

Inserting a cell in the header.
We are trying to get a cell in the header of a sheet. Can this be done? Example would be in our sheet in D9 is TFF. We want whatever is in D9 to go in the header. I'm not sure this makes sense. Thanks Wendy You could do this with a macro linked to the worksheet_change event. Insert the following code into the macro sheet for the sheet with the cell if interest. Private Sub Worksheet_Change(ByVal Target As Range) For Each Cell In Target If Cell.Address = "$D$9" Then ActiveSheet.PageSetup.CenterHeader = Cell.Value End If Next Cell End Sub -- mrice Research Scientist with ...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Conditional null value
Wondering if anyone can suggest a way to simplify the following code, which I’m using in the OnFormat event of a report. If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me![PtAcct#]), ".", " ") End If If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field194] = Replace(DLookup("[ICD...

Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...

Display ALL text contents in cell
I am having trouble displaying all the contents in the cell. It is som lengthy information in the cell. I have made the row height as high a it will go, but it still won't display all the information. I canno make the column width any wider than I have it set, because then th other columns will not fit on the page. Does anyone know how t overcome this problem? : -- Flipper106 ----------------------------------------------------------------------- Flipper1067's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2561 View this thread: http://www.excelforum.com/...

Clipart will not scale with array of cells
I want to print out an array of cells, some of which contain clipart. When I try to scale up the array to fill the printed page, the clipart scales differently (the clipart objects move down on the page from their normal position within the cells). The artwork size does not change, nor its spacing; it's as though the clipart is reacting to a larger top margin. ...

XML Receipt and Conditions
I wanted to make a small adjustment to the receipt when a certain condition is met. If a particular word (eg. family) is typed into the comment on an item I wanted something slightly different to happen to the receipt I've just about got it, just stuck on the condition tag <IF> <CONDITION>len(Entry.Comment)</CONDITION> <THEN> I can get it using len but this makes it happen for any word, is there anyway I can make it so that it is more specific ie Entry.Comment contains '%family%' try <IF> <CONDITION>Entry.Comment like '%family%'&...

conditionally restricting values in excel
Hello, I want to restrict values in a particular column based on the value in other column. For eg. if column A has value int then column B should be restricted to values 1,2,3,4,5 if column A has value bool then column B should be restricted to values true and false. if column A has value string I dont want any validation to be done and the list box should not appear. Is this possible..... How do I achieve this ? Can I use macros to do this? How? Thanks Hi as a starting point: http://www.contextures.com/xlDataVal02.html >-----Original Message----- >Hello, >I want to restrict ...

Conditional formating condition..?
Hi All I have 2 CF conditions associated with a cell (C14), one of which is: Formula is =OR($C$4="Fred SOMEBODY",C14<>"10:30:00") What happens when this is true isn't important/relevant. The value in the cell appears as 10:30 (formatted as custom/hh:mm) but the value in the 'formula window' is actually 10:30:00. This type of condition works fine when the cell contains a simple number (or text) but I can't get it to work for either times or dates. I have tried it with and without the inverted commas (around the time value) and have tried using 10:30 ...

using named cells across worksheets
I've named various cells after producing a multi-worksheet model. The named cells can be applied in the worksheet they are in but I can't see how to apply them across other worksheets. At the moment, the only option I can think of is manually updating every formula in all the connected worksheets! Any ideas. Nick You defined the names so that the names included the worksheet name, too? Sheet1!test sheet2!test .... Sheetx!test Then you can just use them by making sure you include the complete name: =sum(sheet1!test,sheet2!test,sheetn!test) Is that what you meant??? Nick Malon...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Condition Formatting!
How do I make a row of cell (shading) that change to red color when the word "trial" or "limit" is entered? i.e cell 3f contain the word "trial". From the column of trial to the 1st column, the entire row 3a to 3f will be red? a b c d e f 1 2 3 Trial 4 5 6 Here's some VBA you could paste into the codebehind page for that sheet: Private Sub Worksheet_Change(ByVal Target As Range) If UCase(Target.Value) = "TRIAL" Or UCase(Target.Value) = "LIMIT" T...

Formatting cells for printing
How can you add a blank line or bottom margin in a cell to seperate the rows for printing? In Word it is called a cell margin. You can increase the rowheight by selecting a cell in that row and then: format|row|height and making it larger. You could also add an alt-enter as the last character in that cell. (might be messy if you're working with numeric values, though) Steve S wrote: > > How can you add a blank line or bottom margin in a cell to seperate the rows > for printing? In Word it is called a cell margin. -- Dave Peterson ...