MIN value exclude '0' & Conditional Formatting

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"JEM" wrote in message
...
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!

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.

--

Vasant





"JEM" wrote in message
...
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!

You can use array function: =MIN(IF(B1:B40,B1:B4,""))
Array function -- instead of pressing "ENTER", press Ctrl-Shft-Enter.

"JEM" wrote in message
...
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!

Thanks for the quick responses! I won't dig into it for a few days. I'll let
you know what happens.


"Amir" wrote in message
...
You can use array function: =MIN(IF(B1:B40,B1:B4,""))
Array function -- instead of pressing "ENTER", press Ctrl-Shft-Enter.

"JEM" wrote in message
...
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!

Thanks for all your help....

"JEM" wrote in message
...
Thanks for the quick responses! I won't dig into it for a few days. I'll
let
you know what happens.


"Amir" wrote in message
...
You can use array function: =MIN(IF(B1:B40,B1:B4,""))
Array function -- instead of pressing "ENTER", press Ctrl-Shft-Enter.

"JEM" wrote in message
...
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!