Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel conditional formating decimal fractions
I am using excel 2003. I have set conditional formating to turn a cell green
when the cell value is the same as another cell value. The source cell contains a formula that delivers a £value which I have set to 2 decimal places.(£.pp) This is a financial value say £2.50 (£2.4990) I then enter a financial value in the comparison cell as £2.50, which I want it to identify as the same as the above value £2.50 (£2.4990) It appears that the conditional formating is comparing the full numerical value in both boxes, and therefore not detecting a "same as" condition. I have verified this by removing the 2 decimal places formating and the cell shows the full value, when I enter the same value the "same as" value conditional format is triggered. I could remove the 2 decimal places format permanently, which would resolve the conditional format problem, however as this is a financial calculation I would prefer to retain the 2 decimal places format. Can anyone help in identifying a solution. Many thanks Andy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel conditional formating decimal fractions
Hi Bob,
Thankyou for your reply. I am sure what you have said is the answer I am looking for however I seem unable to translate your reply into the conditional formating input box. the conditional formatting option I have at present completed is (which works for whole numbers) Cell Value is equal to =$E$8 [E8 being the original variable target cell] From your reply I am unable to identify how to include ROUND into the input field. Hope this makes sense and you are able to assist. Thanks again Andy "Bob Phillips" wrote: Test against the rounded version, just as the formatting does =A1=ROUND(B1,2) for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" <Andy wrote in message ... I am using excel 2003. I have set conditional formating to turn a cell green when the cell value is the same as another cell value. The source cell contains a formula that delivers a £value which I have set to 2 decimal places.(£.pp) This is a financial value say £2.50 (£2.4990) I then enter a financial value in the comparison cell as £2.50, which I want it to identify as the same as the above value £2.50 (£2.4990) It appears that the conditional formating is comparing the full numerical value in both boxes, and therefore not detecting a "same as" condition. I have verified this by removing the 2 decimal places formating and the cell shows the full value, when I enter the same value the "same as" value conditional format is triggered. I could remove the 2 decimal places format permanently, which would resolve the conditional format problem, however as this is a financial calculation I would prefer to retain the 2 decimal places format. Can anyone help in identifying a solution. Many thanks Andy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel conditional formating decimal fractions
Andy,
In the CF, change Condition 1 to Formula Is and then put the formula in, which assuming you are adding CF to say A1 and the cell with 2.50 is B1, would be =ROUND(A1,2)=B1 just change to your cells -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" wrote in message ... Hi Bob, Thankyou for your reply. I am sure what you have said is the answer I am looking for however I seem unable to translate your reply into the conditional formating input box. the conditional formatting option I have at present completed is (which works for whole numbers) Cell Value is equal to =$E$8 [E8 being the original variable target cell] From your reply I am unable to identify how to include ROUND into the input field. Hope this makes sense and you are able to assist. Thanks again Andy "Bob Phillips" wrote: Test against the rounded version, just as the formatting does =A1=ROUND(B1,2) for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" <Andy wrote in message ... I am using excel 2003. I have set conditional formating to turn a cell green when the cell value is the same as another cell value. The source cell contains a formula that delivers a £value which I have set to 2 decimal places.(£.pp) This is a financial value say £2.50 (£2.4990) I then enter a financial value in the comparison cell as £2.50, which I want it to identify as the same as the above value £2.50 (£2.4990) It appears that the conditional formating is comparing the full numerical value in both boxes, and therefore not detecting a "same as" condition. I have verified this by removing the 2 decimal places formating and the cell shows the full value, when I enter the same value the "same as" value conditional format is triggered. I could remove the 2 decimal places format permanently, which would resolve the conditional format problem, however as this is a financial calculation I would prefer to retain the 2 decimal places format. Can anyone help in identifying a solution. Many thanks Andy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel conditional formating decimal fractions
Hi Bob,
Thanks again. I have still not been able to make this conditional formating work. Based on your suggestions I have entered Formulae is =ROUND(E9)=E8 [where e9 is the freetext box- the one I want to turn the cell background to be green-and where I enter £2.50 and E8 is the box that is delivering a calculated figure of £2.50 (actually 2.4990 - but expressed to 2 decimal places) As stated I have entered the above which is what I have deduced from your assistance but this has not worked. If anyone can spot where I am going wrong that would be much appreciated. Regards Andy "Bob Phillips" wrote: Andy, In the CF, change Condition 1 to Formula Is and then put the formula in, which assuming you are adding CF to say A1 and the cell with 2.50 is B1, would be =ROUND(A1,2)=B1 just change to your cells -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" wrote in message ... Hi Bob, Thankyou for your reply. I am sure what you have said is the answer I am looking for however I seem unable to translate your reply into the conditional formating input box. the conditional formatting option I have at present completed is (which works for whole numbers) Cell Value is equal to =$E$8 [E8 being the original variable target cell] From your reply I am unable to identify how to include ROUND into the input field. Hope this makes sense and you are able to assist. Thanks again Andy "Bob Phillips" wrote: Test against the rounded version, just as the formatting does =A1=ROUND(B1,2) for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" <Andy wrote in message ... I am using excel 2003. I have set conditional formating to turn a cell green when the cell value is the same as another cell value. The source cell contains a formula that delivers a £value which I have set to 2 decimal places.(£.pp) This is a financial value say £2.50 (£2.4990) I then enter a financial value in the comparison cell as £2.50, which I want it to identify as the same as the above value £2.50 (£2.4990) It appears that the conditional formating is comparing the full numerical value in both boxes, and therefore not detecting a "same as" condition. I have verified this by removing the 2 decimal places formating and the cell shows the full value, when I enter the same value the "same as" value conditional format is triggered. I could remove the 2 decimal places format permanently, which would resolve the conditional format problem, however as this is a financial calculation I would prefer to retain the 2 decimal places format. Can anyone help in identifying a solution. Many thanks Andy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel conditional formating decimal fractions
Andy,
You have to specify how many places to round to, so use =ROUND(E9,2)=E8 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" wrote in message ... Hi Bob, Thanks again. I have still not been able to make this conditional formating work. Based on your suggestions I have entered Formulae is =ROUND(E9)=E8 [where e9 is the freetext box- the one I want to turn the cell background to be green-and where I enter £2.50 and E8 is the box that is delivering a calculated figure of £2.50 (actually 2.4990 - but expressed to 2 decimal places) As stated I have entered the above which is what I have deduced from your assistance but this has not worked. If anyone can spot where I am going wrong that would be much appreciated. Regards Andy "Bob Phillips" wrote: Andy, In the CF, change Condition 1 to Formula Is and then put the formula in, which assuming you are adding CF to say A1 and the cell with 2.50 is B1, would be =ROUND(A1,2)=B1 just change to your cells -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" wrote in message ... Hi Bob, Thankyou for your reply. I am sure what you have said is the answer I am looking for however I seem unable to translate your reply into the conditional formating input box. the conditional formatting option I have at present completed is (which works for whole numbers) Cell Value is equal to =$E$8 [E8 being the original variable target cell] From your reply I am unable to identify how to include ROUND into the input field. Hope this makes sense and you are able to assist. Thanks again Andy "Bob Phillips" wrote: Test against the rounded version, just as the formatting does =A1=ROUND(B1,2) for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" <Andy wrote in message ... I am using excel 2003. I have set conditional formating to turn a cell green when the cell value is the same as another cell value. The source cell contains a formula that delivers a £value which I have set to 2 decimal places.(£.pp) This is a financial value say £2.50 (£2.4990) I then enter a financial value in the comparison cell as £2.50, which I want it to identify as the same as the above value £2.50 (£2.4990) It appears that the conditional formating is comparing the full numerical value in both boxes, and therefore not detecting a "same as" condition. I have verified this by removing the 2 decimal places formating and the cell shows the full value, when I enter the same value the "same as" value conditional format is triggered. I could remove the 2 decimal places format permanently, which would resolve the conditional format problem, however as this is a financial calculation I would prefer to retain the 2 decimal places format. Can anyone help in identifying a solution. Many thanks Andy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel conditional formating decimal fractions
BOB,
Thanks for your help I have now sorted it out. Using your assistance with the ROUND formatting I realised that the number needing rounding was the source calculation, I therefore used the ROUND formatting to deliver numbers to two decimal places elsewhere on the spreadsheet calculation. Therefore all numbers were then all at 2 decimal places ready for when the Same As CF test was run. The cause of my problem was thinking that when setting Cell Format to 2 decimal places this returned that value, clearly it only effects the display not the actual cell value. I have now discovered that this needed to be achieved by using the ROUND formatting. Thanks again for your help. Regards Andy "Bob Phillips" wrote: Andy, You have to specify how many places to round to, so use =ROUND(E9,2)=E8 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" wrote in message ... Hi Bob, Thanks again. I have still not been able to make this conditional formating work. Based on your suggestions I have entered Formulae is =ROUND(E9)=E8 [where e9 is the freetext box- the one I want to turn the cell background to be green-and where I enter £2.50 and E8 is the box that is delivering a calculated figure of £2.50 (actually 2.4990 - but expressed to 2 decimal places) As stated I have entered the above which is what I have deduced from your assistance but this has not worked. If anyone can spot where I am going wrong that would be much appreciated. Regards Andy "Bob Phillips" wrote: Andy, In the CF, change Condition 1 to Formula Is and then put the formula in, which assuming you are adding CF to say A1 and the cell with 2.50 is B1, would be =ROUND(A1,2)=B1 just change to your cells -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" wrote in message ... Hi Bob, Thankyou for your reply. I am sure what you have said is the answer I am looking for however I seem unable to translate your reply into the conditional formating input box. the conditional formatting option I have at present completed is (which works for whole numbers) Cell Value is equal to =$E$8 [E8 being the original variable target cell] From your reply I am unable to identify how to include ROUND into the input field. Hope this makes sense and you are able to assist. Thanks again Andy "Bob Phillips" wrote: Test against the rounded version, just as the formatting does =A1=ROUND(B1,2) for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Dixon" <Andy wrote in message ... I am using excel 2003. I have set conditional formating to turn a cell green when the cell value is the same as another cell value. The source cell contains a formula that delivers a £value which I have set to 2 decimal places.(£.pp) This is a financial value say £2.50 (£2.4990) I then enter a financial value in the comparison cell as £2.50, which I want it to identify as the same as the above value £2.50 (£2.4990) It appears that the conditional formating is comparing the full numerical value in both boxes, and therefore not detecting a "same as" condition. I have verified this by removing the 2 decimal places formating and the cell shows the full value, when I enter the same value the "same as" value conditional format is triggered. I could remove the 2 decimal places format permanently, which would resolve the conditional format problem, however as this is a financial calculation I would prefer to retain the 2 decimal places format. Can anyone help in identifying a solution. Many thanks Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent Excel converting imported fractions into dates | Excel Discussion (Misc queries) | |||
Why do conditional formats appear by themselves in Excel 2003? | Excel Discussion (Misc queries) | |||
Excel adds phantom decimal places: why? | Excel Discussion (Misc queries) | |||
conditional formating | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) |