|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 23rd, 2009, 07:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? 
|
|
#2
June 23rd, 2009, 07:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#3
June 23rd, 2009, 07:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#4
June 23rd, 2009, 07:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#5
June 23rd, 2009, 08:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#6
June 23rd, 2009, 08:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#7
June 23rd, 2009, 08:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#8
June 23rd, 2009, 08:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#9
June 23rd, 2009, 08:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
