MIN value exclude '0' & Conditional Formatting

  • Thread starter Thread starter JEM
  • Start date Start date
J

JEM

MIN value question:
I have a spread sheet for grades. I have set up two cells one to give me the
MIN value and the other to give me the MAX value. That way I can quickly see
the lowest and highest grades. Of course, the MAX value is fine, it reports
the top score. However, the MIN value always returns '0' because in some
cases, students or classes have not tested and no score has been entered.

In the following example, how can I set up the MIN formula to '60' the
lowest score, and not '0', the lowest value. Do I have to add something to
the formula, or use a different one?

A------ B
Student Score
1------ 80
2------100
3------ 0 (cell is blank - not tested)
4------ 60

Conditional formatting question:

The columns for the test score are formatted with green fill.

I have a conditional formatting set to:
Cell Value is / equal to / 0 (format / fill / blue)

So cells with a '0' value are blue and entering a test score turns the cell
green. (This makes it very easy to scroll through the sheet and see who has
taken the test or not, or if there was a mistake during input.) However,
let's say the student gets a zero on the test, and a 0 is entered in the
cell.... it stay blue. How can I set up Conditional formatting like this:

nothing in the cell = blue
something in the cell, including '0' green.

OR, what I'd really like

nothing in the cell = blue
something in the cell = green
0 in the cell = red

Do I need to use a formula instead of cell value?

Thanks!
 
Hi JEM

Select the range of cells
Format > Cells > Patterns
Select green
OK

Select the range of cells
Format > Conditional Format
Formula is: =ISBLANK(A1) [use cell reference of first cell in the
range]
Format button
Pattern
Select blue
OK
Add
Cell Value Is:
Equal to
0
Format button
Pattern
Select red
OK
OK

Green becomes the default value with green for all entries
Blue is applied to blank cells
Red is applied to 0 cells

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
For the minimum, use the array formula:

=MIN(IF(A1:A4<>"",A1:A4))

entered with <Ctrl> <Shift> <Enter>.

For the conditional formatting:

Condition 1: Formula Is =A1="" (blue)
Condition 2: Cell value is equal to 0 (red)

The default format for the range should be green.
 
You can use array function: =MIN(IF(B1:B4>0,B1:B4,""))
Array function -- instead of pressing "ENTER", press Ctrl-Shft-Enter.
 
Thanks for the quick responses! I won't dig into it for a few days. I'll let
you know what happens.
 
Back
Top