Conditional [Color] Formatting not recognized with formula

[torgerjl]

Two questions really...
1) I have entered a formula (below) to calculate a time field and if it shows up 104% (4% overdue) I want to turn it red; when I try to add a condition if >100 then red it does not work. Any suggestions?
=ROUND(((K10*100)/J10),0)&"%"

2) I am having a similar issue counting this field. For example, if I enter the formula below it always equals '0'
=COUNTIF(W10:W60,"<(100)")

[MaximS]

Try this:

1. Format>>Conditional formatting>>Cells Value >>Greater than >> 1

2. Use below function:

[VBA]=SUMPRODUCT((W10:W60<100)*(W10:W60)) [/VBA]

[torgerjl]

Neither work. It turns anything red that I apply the condition to in that cell range. The SUM funtion does not work either. Do you think it is because of the formatting in the formula itself eg, =ROUND(((K10*100)/J10),0)&"%" ?

[Bob Phillips]

It is because your cell has a text value not a number.

You should use a formula that calculates a REAL number and format it as percentage

=ROUND(K10/J10,2)

and then test this for > 1.04 to test for greater than 104%.

[torgerjl]

What do you do if you are working with a delivery date and a report date? For example, days complete divided by deliverable difference.

[Bob Phillips]

What do you want to do?

[torgerjl]

Right now it works as so. I am working with three dates: 1) Start date, 2) End date and 3) Report date. I have three hidden cells: 1) Days Complete, 2) Deliverable Difference and 3) Report Date.

Hidden Cells:
The days complete is the Deliverable difference less the Report Date. The Deliverable difference is the End date less the Start Date. Obviously, the Report date is the current month (eg, 8-31-08) less the End date.

Then finally, the =ROUND formula =ROUND(((K10*100)/J10),0)&"%

[Bob Phillips]

I ask again, what do you want to do? You have given me some background,m but not told me what you are trying to do. What is K10? What is J10? Did you read my earler post?

[torgerjl]

Conditional formatting and counting that works. K10 is the Days Complete and J10 is the Deliverable Difference.