excel conditional formating decimal fractions

[Andy Dixon]

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

 

[Bob Phillips]

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]

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]

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)

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

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)

cell
green I
want the
cell calculation
I

 

[Andy Dixon]

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

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)

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

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)

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

 

[Bob Phillips]

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)

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

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)

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






:

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)

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

 

[Andy Dixon]

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

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)

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

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)

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






:

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)

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