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

excel conditional formating decimal fractions
 

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

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

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

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

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

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