Conditional Formatting Problem
 

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

Hi,
Try

=round(=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),0)

"mrogozinski" wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

Hi,
try
=round((SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),0)

if you want to keep the decimals in the conditional formating you will have
to use decimals between 4.4909999

if this helps please click yes, thanks

"mrogozinski" wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

It did not solve the problem. In fact, with the 2 ='s signs, it gave me a
formula error.

"Eduardo" wrote:

Hi,
Try

=round(=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),0)

"mrogozinski" wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

Pretty well the same suggestions you got earlier to the same post.

Have you tried ROUNDING the results?

Your formula can be written as

=ROUND((J12*$J$15+K12*$K$15)/SUM($J$15:$K$15),0)

No need for the two extra SUM's


Gord Dibben MS Excel MVP


On Tue, 23 Jun 2009 11:21:02 -0700, mrogozinski
wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

Why not simplify your formula to something like this:

=ROUND((J12*J15+K12*K15)/(J15+K15),1)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mrogozinski" wrote in message
...
It did not solve the problem. In fact, with the 2 ='s signs, it gave me a
formula error.

"Eduardo" wrote:

Hi,
Try

=round(=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),0)

"mrogozinski" wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell
"green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally
to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

As RD points out.............no need for any SUM's

I mis-read the last range as more than two cells


Gord

On Tue, 23 Jun 2009 12:09:04 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Pretty well the same suggestions you got earlier to the same post.

Have you tried ROUNDING the results?

Your formula can be written as

=ROUND((J12*$J$15+K12*$K$15)/SUM($J$15:$K$15),0)

No need for the two extra SUM's


Gord Dibben MS Excel MVP


On Tue, 23 Jun 2009 11:21:02 -0700, mrogozinski
wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

Thought I'd share what worked.

-ROUND((SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),2)
"Eduardo" wrote:

Hi,
Try

=round(=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),0)

"mrogozinski" wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell "green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?

Conditional Formatting Problem
 

See below for the round formula that worked. Thank you.

"RagDyer" wrote:

Why not simplify your formula to something like this:

=ROUND((J12*J15+K12*K15)/(J15+K15),1)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mrogozinski" wrote in message
...
It did not solve the problem. In fact, with the 2 ='s signs, it gave me a
formula error.

"Eduardo" wrote:

Hi,
Try

=round(=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15),0)

"mrogozinski" wrote:

One of my conditional formatting rules is as follows:

If value of the cell is between 4.5 and 5.0 then format the cell
"green".
The problem is the formula calculates the result as 4.497947455 (which
rounded is technically 4.5) and this cell does not format conditionally
to
green.

Here is the formula:

=(SUM(J12*$J$15)+SUM(K12*$K$15))/SUM($J$15:$K$15)


Any suggestions on how to get around that?