Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - help!
I am testing 10 thermometers and using Excel for my data. There is a +/-3°
allowable tolerance. Column A is my set scale (-5°C to +5°C) and columns B through K are my result figures for these devices. I'd like to conditionally format the cells of the results that are too low (-3) blue and if they are too high (+3) format the red. Thank you for your help in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - help!
What is your question? Have you looked at help for conditional formatting?
What don't you understand? -- David Biddulph "Rezendes" wrote in message ... I am testing 10 thermometers and using Excel for my data. There is a +/-3° allowable tolerance. Column A is my set scale (-5°C to +5°C) and columns B through K are my result figures for these devices. I'd like to conditionally format the cells of the results that are too low (-3) blue and if they are too high (+3) format the red. Thank you for your help in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - help!
My first reference value is in A2 (that has -5)
I selected B2:K11 and in the Conditional Formatting dialog used Formula Is =B2-$A23 and set font colour to red For the second one I used Formula Is =$A2-B23 and set font colour to blue You must have numbers in all cells but you could use Custom Format of: 0 "°C" to display -5 as -5°C best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rezendes" wrote in message ... I am testing 10 thermometers and using Excel for my data. There is a +/-3° allowable tolerance. Column A is my set scale (-5°C to +5°C) and columns B through K are my result figures for these devices. I'd like to conditionally format the cells of the results that are too low (-3) blue and if they are too high (+3) format the red. Thank you for your help in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - h
Thank you Bernard!
If you would be so kind, I have a follow up question because I am unable to create one additional CF rule. For reference, my actual tolerance is +- 1°C. With your help, my spreadsheet now shows temps that are too low in blue, too high in red - thank you! I'd like to add one more CF rule where the cell values X (when compared to column A) are yellow under these conditions (both positive and negative - perhaps using absolute value?): 1X.5 My trial and error attempts have all been errors to this point! Thank you again for your invaluable assistance! "Bernard Liengme" wrote: My first reference value is in A2 (that has -5) I selected B2:K11 and in the Conditional Formatting dialog used Formula Is =B2-$A23 and set font colour to red For the second one I used Formula Is =$A2-B23 and set font colour to blue You must have numbers in all cells but you could use Custom Format of: 0 "°C" to display -5 as -5°C best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rezendes" wrote in message ... I am testing 10 thermometers and using Excel for my data. There is a +/-3° allowable tolerance. Column A is my set scale (-5°C to +5°C) and columns B through K are my result figures for these devices. I'd like to conditionally format the cells of the results that are too low (-3) blue and if they are too high (+3) format the red. Thank you for your help in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - h
I think this is what you want
=AND(ABS($A2-B2)=0.5,ABS($A2-B2)<=1) Be careful with colours. Many people (men) are red colour blind and yellow is really hard to read I experimented: gave all the cells a light grey fill and in conditional formatting I used coloured borders rather than font colour. Looked quite nice! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rezendes" wrote in message ... Thank you Bernard! If you would be so kind, I have a follow up question because I am unable to create one additional CF rule. For reference, my actual tolerance is +- 1°C. With your help, my spreadsheet now shows temps that are too low in blue, too high in red - thank you! I'd like to add one more CF rule where the cell values X (when compared to column A) are yellow under these conditions (both positive and negative - perhaps using absolute value?): 1X.5 My trial and error attempts have all been errors to this point! Thank you again for your invaluable assistance! "Bernard Liengme" wrote: My first reference value is in A2 (that has -5) I selected B2:K11 and in the Conditional Formatting dialog used Formula Is =B2-$A23 and set font colour to red For the second one I used Formula Is =$A2-B23 and set font colour to blue You must have numbers in all cells but you could use Custom Format of: 0 "°C" to display -5 as -5°C best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rezendes" wrote in message ... I am testing 10 thermometers and using Excel for my data. There is a +/-3° allowable tolerance. Column A is my set scale (-5°C to +5°C) and columns B through K are my result figures for these devices. I'd like to conditionally format the cells of the results that are too low (-3) blue and if they are too high (+3) format the red. Thank you for your help in advance! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - h
Bernard,
Thank you for the advice on color usage. My spreadsheet is a couple of printed pages long. The effect I'm after with the colors is to easily identify over and under spec conditions (+-1°C) as well as "warning sign" conditions (=- .5° to +-.9°). More than 95% of the values are within spec so there is no color there. With your help, the out of spec cells stand out quite nicely and will help the engineers correct the calibration curve easily. The yellow formula doesn't seem to be working as expected - I have temp values that are less than +-.5 that are getting the yellow when that should not be the case and values that should be yellow are not. I highlighted my entire data set, added the yellow rule as you wrote it. I'm simply not sure why it doesn't perform correctly. Any additional assistance would be greatly appreciated! Thank you a thousand times over for your help! "Bernard Liengme" wrote: I think this is what you want =AND(ABS($A2-B2)=0.5,ABS($A2-B2)<=1) Be careful with colours. Many people (men) are red colour blind and yellow is really hard to read I experimented: gave all the cells a light grey fill and in conditional formatting I used coloured borders rather than font colour. Looked quite nice! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tolerances in a temperature chart - conditional formatting - h
Maybe its time for us to work off-group. Send me a sample file; just remove
TRUENORTH. for the address showing in this message. I have a working file that does what I think you want but I may have missed something. best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rezendes" wrote in message ... Bernard, Thank you for the advice on color usage. My spreadsheet is a couple of printed pages long. The effect I'm after with the colors is to easily identify over and under spec conditions (+-1°C) as well as "warning sign" conditions (=- .5° to +-.9°). More than 95% of the values are within spec so there is no color there. With your help, the out of spec cells stand out quite nicely and will help the engineers correct the calibration curve easily. The yellow formula doesn't seem to be working as expected - I have temp values that are less than +-.5 that are getting the yellow when that should not be the case and values that should be yellow are not. I highlighted my entire data set, added the yellow rule as you wrote it. I'm simply not sure why it doesn't perform correctly. Any additional assistance would be greatly appreciated! Thank you a thousand times over for your help! "Bernard Liengme" wrote: I think this is what you want =AND(ABS($A2-B2)=0.5,ABS($A2-B2)<=1) Be careful with colours. Many people (men) are red colour blind and yellow is really hard to read I experimented: gave all the cells a light grey fill and in conditional formatting I used coloured borders rather than font colour. Looked quite nice! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
engineering tolerances | Excel Discussion (Misc queries) | |||
Chart Conditional Formatting | Excel Discussion (Misc queries) | |||
HOW DO I USE EXCEL FOR QUALITY CONTROL CHART SUCH AS TEMPERATURE . | Charts and Charting in Excel | |||
How do I enter temperature symbol in a chart legent | Charts and Charting in Excel | |||
How do I get EXCEL to experss tolerances? | Excel Discussion (Misc queries) |