If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#3
|
|||
|
|||
MIN value exclude '0' & Conditional Formatting
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! |
#4
|
|||
|
|||
MIN value exclude '0' & Conditional Formatting
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! |
#5
|
|||
|
|||
MIN value exclude '0' & Conditional Formatting
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! |
#6
|
|||
|
|||
MIN value exclude '0' & Conditional Formatting
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! |
Thread Tools | |
Display Modes | |
|
|