Conditional Formatting Problem

  • Thread starter Thread starter mrogozinski
  • Start date Start date
M

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?
 
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
 
It did not solve the problem. In fact, with the 2 ='s signs, it gave me a
formula error.
 
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
 
Why not simplify your formula to something like this:

=ROUND((J12*J15+K12*K15)/(J15+K15),1)
 
As RD points out.............no need for any SUM's

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


Gord
 
Thought I'd share what worked.

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