Conditional Formating

[Danny]

I have cells in a worksheet that brings in numbers (linked to the cell)from
other sheets in the work book. The values being brought in a simple numbers,
averaged from a small set of other numbers.

All number formats are set to number, 0 decimal points.

The roll up table is showing the correct, rounded values to the nearest
whole number that are linked back to cells in the supporting work sheet.

However, the problem appears to be that conditonal formating is looking at
the worsheet values and applying the format to the un rounded values.

For example, if I tell the roll up table to format red each cell that has a
value between 3 & 3.99. The table shows side by side cells that both contain
the number 3. However one cell is red and the other is not. If you go back
to the cell of origin for each, it will show that the unrounded value for one
cell may be 2.8, and 3.5 for the other. The condition is applied based on
the decimal points, even though the origin cells are fomatted with no decimal
places.

Thanks in advance,

Danny

[JulieD]

Hi Danny

numbers formatted to hide the decimal places aren't actually rounded they
just look that way
try this
type in A1
57.6
and format it to show no decimal places now in B1 type
=A1*100
you will see that the original number is multiplied by 100 not the formatted
number

to round a number use the round function
=ROUND(A1,0)

unfortunately, i can't think of an easy way to solve your problem unless you
use tools / options / calculation, precision as displayed ... however, the
following warning is given in HELP
--
Caution When you change the precision of the calculations in a workbook by
using the displayed (formatted) values, Excel permanently changes any
constant values on the worksheets in the workbook. If you later choose to
calculate with full precision, the original underlying values cannot be
restored.
---
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Danny" <[email protected]> wrote in message
news:[email protected]...
>I have cells in a worksheet that brings in numbers (linked to the cell)from
> other sheets in the work book. The values being brought in a simple
> numbers,
> averaged from a small set of other numbers.
>
> All number formats are set to number, 0 decimal points.
>
> The roll up table is showing the correct, rounded values to the nearest
> whole number that are linked back to cells in the supporting work sheet.
>
> However, the problem appears to be that conditonal formating is looking at
> the worsheet values and applying the format to the un rounded values.
>
> For example, if I tell the roll up table to format red each cell that has
> a
> value between 3 & 3.99. The table shows side by side cells that both
> contain
> the number 3. However one cell is red and the other is not. If you go
> back
> to the cell of origin for each, it will show that the unrounded value for
> one
> cell may be 2.8, and 3.5 for the other. The condition is applied based on
> the decimal points, even though the origin cells are fomatted with no
> decimal
> places.
>
> Thanks in advance,
>
> Danny

[Danny]

Thanks and right oh! The cell values is format as desired, i.e. whole
numbers only, but the conditinal format goes back to the "behind the scenes"
cell value, even though the refeenced cell in the link is also formatted to a
whole number. Will give your suggestion a try and let you know.

"JulieD" wrote:

> Hi Danny
>
> numbers formatted to hide the decimal places aren't actually rounded they
> just look that way
> try this
> type in A1
> 57.6
> and format it to show no decimal places now in B1 type
> =A1*100
> you will see that the original number is multiplied by 100 not the formatted
> number
>
> to round a number use the round function
> =ROUND(A1,0)
>
> unfortunately, i can't think of an easy way to solve your problem unless you
> use tools / options / calculation, precision as displayed ... however, the
> following warning is given in HELP
> --
> Caution When you change the precision of the calculations in a workbook by
> using the displayed (formatted) values, Excel permanently changes any
> constant values on the worksheets in the workbook. If you later choose to
> calculate with full precision, the original underlying values cannot be
> restored.
> ---
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Danny" <[email protected]> wrote in message
> news:[email protected]...
> >I have cells in a worksheet that brings in numbers (linked to the cell)from
> > other sheets in the work book. The values being brought in a simple
> > numbers,
> > averaged from a small set of other numbers.
> >
> > All number formats are set to number, 0 decimal points.
> >
> > The roll up table is showing the correct, rounded values to the nearest
> > whole number that are linked back to cells in the supporting work sheet.
> >
> > However, the problem appears to be that conditonal formating is looking at
> > the worsheet values and applying the format to the un rounded values.
> >
> > For example, if I tell the roll up table to format red each cell that has
> > a
> > value between 3 & 3.99. The table shows side by side cells that both
> > contain
> > the number 3. However one cell is red and the other is not. If you go
> > back
> > to the cell of origin for each, it will show that the unrounded value for
> > one
> > cell may be 2.8, and 3.5 for the other. The condition is applied based on
> > the decimal points, even though the origin cells are fomatted with no
> > decimal
> > places.
> >
> > Thanks in advance,
> >
> > Danny
>
>
>

[Bob Phillips]

Could you not use a CF formula of say

=AND(ROUND(A1,0)>=3,ROUND(A1,0)<4)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Danny
>
> numbers formatted to hide the decimal places aren't actually rounded they
> just look that way
> try this
> type in A1
> 57.6
> and format it to show no decimal places now in B1 type
> =A1*100
> you will see that the original number is multiplied by 100 not the
formatted
> number
>
> to round a number use the round function
> =ROUND(A1,0)
>
> unfortunately, i can't think of an easy way to solve your problem unless
you
> use tools / options / calculation, precision as displayed ... however, the
> following warning is given in HELP
> --
> Caution When you change the precision of the calculations in a workbook
by
> using the displayed (formatted) values, Excel permanently changes any
> constant values on the worksheets in the workbook. If you later choose to
> calculate with full precision, the original underlying values cannot be
> restored.
> ---
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "Danny" <[email protected]> wrote in message
> news:[email protected]...
> >I have cells in a worksheet that brings in numbers (linked to the
cell)from
> > other sheets in the work book. The values being brought in a simple
> > numbers,
> > averaged from a small set of other numbers.
> >
> > All number formats are set to number, 0 decimal points.
> >
> > The roll up table is showing the correct, rounded values to the nearest
> > whole number that are linked back to cells in the supporting work sheet.
> >
> > However, the problem appears to be that conditonal formating is looking
at
> > the worsheet values and applying the format to the un rounded values.
> >
> > For example, if I tell the roll up table to format red each cell that
has
> > a
> > value between 3 & 3.99. The table shows side by side cells that both
> > contain
> > the number 3. However one cell is red and the other is not. If you go
> > back
> > to the cell of origin for each, it will show that the unrounded value
for
> > one
> > cell may be 2.8, and 3.5 for the other. The condition is applied based
on
> > the decimal points, even though the origin cells are fomatted with no
> > decimal
> > places.
> >
> > Thanks in advance,
> >
> > Danny
>
>

[Danny]

Julie D.

tried your suggestion & it worked just fine! Am simply averaging some basic
scoring with rates from 1 to 5 so did not have to worry about losing
precision and the values were not further linked or utilizied so no worry
about downstream precision or accuracy. Used conditional format to color a
cell with green, yellow or red based on the score in order to highlight areas
that need improvements.

Bob - thanks for the idea, but a cf does not fit into the end result I
needed and there are more than the two conditons of T or F.

"Bob Phillips" wrote:

> Could you not use a CF formula of say
>
> =AND(ROUND(A1,0)>=3,ROUND(A1,0)<4)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JulieD" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Danny
> >
> > numbers formatted to hide the decimal places aren't actually rounded they
> > just look that way
> > try this
> > type in A1
> > 57.6
> > and format it to show no decimal places now in B1 type
> > =A1*100
> > you will see that the original number is multiplied by 100 not the
> formatted
> > number
> >
> > to round a number use the round function
> > =ROUND(A1,0)
> >
> > unfortunately, i can't think of an easy way to solve your problem unless
> you
> > use tools / options / calculation, precision as displayed ... however, the
> > following warning is given in HELP
> > --
> > Caution When you change the precision of the calculations in a workbook
> by
> > using the displayed (formatted) values, Excel permanently changes any
> > constant values on the worksheets in the workbook. If you later choose to
> > calculate with full precision, the original underlying values cannot be
> > restored.
> > ---
> > --
> > Cheers
> > JulieD
> > check out www.hcts.net.au/tipsandtricks.htm
> > ...well i'm working on it anyway
> > "Danny" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have cells in a worksheet that brings in numbers (linked to the
> cell)from
> > > other sheets in the work book. The values being brought in a simple
> > > numbers,
> > > averaged from a small set of other numbers.
> > >
> > > All number formats are set to number, 0 decimal points.
> > >
> > > The roll up table is showing the correct, rounded values to the nearest
> > > whole number that are linked back to cells in the supporting work sheet.
> > >
> > > However, the problem appears to be that conditonal formating is looking
> at
> > > the worsheet values and applying the format to the un rounded values.
> > >
> > > For example, if I tell the roll up table to format red each cell that
> has
> > > a
> > > value between 3 & 3.99. The table shows side by side cells that both
> > > contain
> > > the number 3. However one cell is red and the other is not. If you go
> > > back
> > > to the cell of origin for each, it will show that the unrounded value
> for
> > > one
> > > cell may be 2.8, and 3.5 for the other. The condition is applied based
> on
> > > the decimal points, even though the origin cells are fomatted with no
> > > decimal
> > > places.
> > >
> > > Thanks in advance,
> > >
> > > Danny
> >
> >
>
>
>

[JulieD]

Hi Danny

glad to hear its solved and thanks for the feedback.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Danny" <[email protected]> wrote in message
news:[email protected]...
> Julie D.
>
> tried your suggestion & it worked just fine! Am simply averaging some
> basic
> scoring with rates from 1 to 5 so did not have to worry about losing
> precision and the values were not further linked or utilizied so no worry
> about downstream precision or accuracy. Used conditional format to color
> a
> cell with green, yellow or red based on the score in order to highlight
> areas
> that need improvements.
>
> Bob - thanks for the idea, but a cf does not fit into the end result I
> needed and there are more than the two conditons of T or F.
>
> "Bob Phillips" wrote:
>
>> Could you not use a CF formula of say
>>
>> =AND(ROUND(A1,0)>=3,ROUND(A1,0)<4)
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "JulieD" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Danny
>> >
>> > numbers formatted to hide the decimal places aren't actually rounded
>> > they
>> > just look that way
>> > try this
>> > type in A1
>> > 57.6
>> > and format it to show no decimal places now in B1 type
>> > =A1*100
>> > you will see that the original number is multiplied by 100 not the
>> formatted
>> > number
>> >
>> > to round a number use the round function
>> > =ROUND(A1,0)
>> >
>> > unfortunately, i can't think of an easy way to solve your problem
>> > unless
>> you
>> > use tools / options / calculation, precision as displayed ... however,
>> > the
>> > following warning is given in HELP
>> > --
>> > Caution When you change the precision of the calculations in a
>> > workbook
>> by
>> > using the displayed (formatted) values, Excel permanently changes any
>> > constant values on the worksheets in the workbook. If you later choose
>> > to
>> > calculate with full precision, the original underlying values cannot be
>> > restored.
>> > ---
>> > --
>> > Cheers
>> > JulieD
>> > check out www.hcts.net.au/tipsandtricks.htm
>> > ...well i'm working on it anyway
>> > "Danny" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have cells in a worksheet that brings in numbers (linked to the
>> cell)from
>> > > other sheets in the work book. The values being brought in a simple
>> > > numbers,
>> > > averaged from a small set of other numbers.
>> > >
>> > > All number formats are set to number, 0 decimal points.
>> > >
>> > > The roll up table is showing the correct, rounded values to the
>> > > nearest
>> > > whole number that are linked back to cells in the supporting work
>> > > sheet.
>> > >
>> > > However, the problem appears to be that conditonal formating is
>> > > looking
>> at
>> > > the worsheet values and applying the format to the un rounded values.
>> > >
>> > > For example, if I tell the roll up table to format red each cell that
>> has
>> > > a
>> > > value between 3 & 3.99. The table shows side by side cells that both
>> > > contain
>> > > the number 3. However one cell is red and the other is not. If you
>> > > go
>> > > back
>> > > to the cell of origin for each, it will show that the unrounded value
>> for
>> > > one
>> > > cell may be 2.8, and 3.5 for the other. The condition is applied
>> > > based
>> on
>> > > the decimal points, even though the origin cells are fomatted with no
>> > > decimal
>> > > places.
>> > >
>> > > Thanks in advance,
>> > >
>> > > Danny
>> >
>> >
>>
>>
>>

[bhofsetz]

Danny,
It looks like the rounding solution worked, but if you are concerned about rounding for downstream calculations why not set up your conditional fortting based on the unrounded results.

ie.
if your value is between 2.51 and 3.50 it will round to 3 and you want it red
if your value is between 3.51 and 4.50 it will round to 4 and you want it yellow
if your value is above 4.51 it will round to 5 and you want it green.

So set up your conditions accordingly.
Rather than value = 3 turns red
have value between 2.51 and 3.50 turns red

etc.

Just a thought.