Conditional Formatting Problem

[mrogozinski]

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?

 

[Eduardo]

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]

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

 

[Gord Dibben]

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

 

[RagDyer]

Why not simplify your formula to something like this:

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

 

[Gord Dibben]

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

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


Gord

 

[mrogozinski]

Thought I'd share what worked.

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

 

[mrogozinski]

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