Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding formula when using conditional formatting?
I have spreadsheet that has conditions and depending on those conditons the
cells changed color. For example: if cell is less than .29 or = to .29 turn the cell red. Now this cell that has the actual number is next to the cell that shows the % and then the color and the conditional cell has a formula in it. =B12/$B$10. Example: Cell B12 =54 and cell B10 has a total formula thus giving me the number 39. How can I put a formula in the cell that already has the =B12/$B$10 that will round the number up say from .46 to .05 thus updating my color? See right now it does not change to the next color even though I decrease the decimal point. I want to round up in the condional formatting. Help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding formula when using conditional formatting?
If I understand you correctly, use
=ROUND(B12/$B$10, 1) to round 0.46 to 0.50 This changes the STORED value while formatting only changes the DISPLAYED value. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "SunshineinFt.Myers" wrote in message ... I have spreadsheet that has conditions and depending on those conditons the cells changed color. For example: if cell is less than .29 or = to .29 turn the cell red. Now this cell that has the actual number is next to the cell that shows the % and then the color and the conditional cell has a formula in it. =B12/$B$10. Example: Cell B12 =54 and cell B10 has a total formula thus giving me the number 39. How can I put a formula in the cell that already has the =B12/$B$10 that will round the number up say from .46 to .05 thus updating my color? See right now it does not change to the next color even though I decrease the decimal point. I want to round up in the condional formatting. Help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding formula when using conditional formatting?
Hi: my number is a % for example: 17.95 and its the color red. At .18 it is
supposed turn light green. I tried your formula and rounded it up to .20%. its close but too much. Any ideas? "Bernard Liengme" wrote: If I understand you correctly, use =ROUND(B12/$B$10, 1) to round 0.46 to 0.50 This changes the STORED value while formatting only changes the DISPLAYED value. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "SunshineinFt.Myers" wrote in message ... I have spreadsheet that has conditions and depending on those conditons the cells changed color. For example: if cell is less than .29 or = to .29 turn the cell red. Now this cell that has the actual number is next to the cell that shows the % and then the color and the conditional cell has a formula in it. =B12/$B$10. Example: Cell B12 =54 and cell B10 has a total formula thus giving me the number 39. How can I put a formula in the cell that already has the =B12/$B$10 that will round the number up say from .46 to .05 thus updating my color? See right now it does not change to the next color even though I decrease the decimal point. I want to round up in the condional formatting. Help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding formula when using conditional formatting?
We need to be careful here; you talk about 17.95% rounded to .20%. Do you
mean 20.00% What is the EXACT setting for your Conditional Formatting? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "SunshineinFt.Myers" wrote in message ... Hi: my number is a % for example: 17.95 and its the color red. At .18 it is supposed turn light green. I tried your formula and rounded it up to .20%. its close but too much. Any ideas? "Bernard Liengme" wrote: If I understand you correctly, use =ROUND(B12/$B$10, 1) to round 0.46 to 0.50 This changes the STORED value while formatting only changes the DISPLAYED value. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "SunshineinFt.Myers" wrote in message ... I have spreadsheet that has conditions and depending on those conditons the cells changed color. For example: if cell is less than .29 or = to .29 turn the cell red. Now this cell that has the actual number is next to the cell that shows the % and then the color and the conditional cell has a formula in it. =B12/$B$10. Example: Cell B12 =54 and cell B10 has a total formula thus giving me the number 39. How can I put a formula in the cell that already has the =B12/$B$10 that will round the number up say from .46 to .05 thus updating my color? See right now it does not change to the next color even though I decrease the decimal point. I want to round up in the condional formatting. Help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding formula when using conditional formatting?
"SunshineinFt.Myers" wrote:
Hi: my number is a % for example: 17.95 and its the color red. At .18 it is supposed turn light green. I tried your formula and rounded it up to .20%. its close but too much. Any ideas? See my response to your subsequent new thread. PS: In the future, do not duplicate questions in multiple threads. ----- original posting ----- "SunshineinFt.Myers" wrote: Hi: my number is a % for example: 17.95 and its the color red. At .18 it is supposed turn light green. I tried your formula and rounded it up to .20%. its close but too much. Any ideas? "Bernard Liengme" wrote: If I understand you correctly, use =ROUND(B12/$B$10, 1) to round 0.46 to 0.50 This changes the STORED value while formatting only changes the DISPLAYED value. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "SunshineinFt.Myers" wrote in message ... I have spreadsheet that has conditions and depending on those conditons the cells changed color. For example: if cell is less than .29 or = to .29 turn the cell red. Now this cell that has the actual number is next to the cell that shows the % and then the color and the conditional cell has a formula in it. =B12/$B$10. Example: Cell B12 =54 and cell B10 has a total formula thus giving me the number 39. How can I put a formula in the cell that already has the =B12/$B$10 that will round the number up say from .46 to .05 thus updating my color? See right now it does not change to the next color even though I decrease the decimal point. I want to round up in the condional formatting. Help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Formula | Excel Worksheet Functions | |||
Conditional Formatting Formula | Excel Worksheet Functions | |||
what formula do I use for conditional formatting? | Excel Worksheet Functions | |||
Conditional Formatting Formula | Excel Worksheet Functions | |||
conditional formatting or formula | Excel Worksheet Functions |