COUNTIF - Count dates that aren't within targeted month & Conditional Formatting

[Bonnie Janes]

Hi All, if anyone can help me with the problems below, and within this document; Excel Formulas_COUNTIF & Conditional Formatting Problems.xlsx I would be eternally grateful!

Problem 1

Services completed early/late – COUNTIF?

ROW 6

I need a formula that counts from the column below dates that are before or after the MONTH of the column. I wrote one that should have worked, but it didn’t, so had to count manually. Need this to be automatic… ideas?



Problem 2

Last Serviced Date – CONDITIONAL FORMATTING FORMULA

COLUMN E

I need a formula that checks the frequency in months (Column C&D), against the date in Last Serviced Column (E) to check if it’s OVERDUE. i.e. if it must be serviced every 6 months and the last serviced date is greater than 6 months then the date cell in column E highlights RED.



Problem 3

Conditional formatting WILL NOT colour numbers on the cusp 89% figure or 50% figure yellow!

Should be

90-100 = Green

51 to 89 = Yellow

50 or less = Red

ROWS 4 & 7

 

[6StringJazzer]

Problem 1

Services completed early/late – COUNTIF?

ROW 6

I need a formula that counts from the column below dates that are before or after the MONTH of the column. I wrote one that should have worked, but it didn’t, so had to count manually. Need this to be automatic… ideas?

I looked at the first column to figure out what you need. Your manual number is 5 but I only counted 4 dates in that column that are not in April. Is that an error in the manual count, or am I misunderstanding the requirement? Same issue for the next three columns.

If I assume your manual count is wrong, then the formula that will give the number of dates in that column that are not in the month in the header is:

=COUNTIF(F10:F999,"<"&F9)+COUNTIF(F10:F999,">"&EOMONTH(F9,0))

Problem 2

Last Serviced Date – CONDITIONAL FORMATTING FORMULA

COLUMN E

I need a formula that checks the frequency in months (Column C&D), against the date in Last Serviced Column (E) to check if it’s OVERDUE. i.e. if it must be serviced every 6 months and the last serviced date is greater than 6 months then the date cell in column E highlights RED.

Greater than 6 months relative to when? Today's date? If so, the CF rule is

=E10<EDATE(TODAY(),-D10)

Problem 3

Conditional formatting WILL NOT colour numbers on the cusp 89% figure or 50% figure yellow!

Should be

90-100 = Green

51 to 89 = Yellow

50 or less = Red

ROWS 4 & 7

It does color 89% yellow. But your display rounds the percentage so that 89.1% appears as 89%, and will not be colored because it is >89%.

The problem you are having is your definition. You are thinking of the percentages as if they are integers, but you are calculating a percentage with division so it is a real number. Your definition above does not account for the real numbers between 89 and 90, or between 50 and 51.

The simplest change you can make to fix this is to change your formula to round to the nearest whole-number percentage. You also should not be using SUM because you are not taking the sum of anything. Also multiplying and dividing by 100 has no effect.
Your formula:

=SUM(F3/F2)*100/100

Correction:

=ROUND(F3/F2,2)

I am not sending back your actual file for you but if you have any trouble with any of these suggestions let me know and I can do that. I have tested these.

 

[Bonnie Janes]

I looked at the first column to figure out what you need. Your manual number is 5 but I only counted 4 dates in that column that are not in April. Is that an error in the manual count, or am I misunderstanding the requirement? Same issue for the next three columns.

If I assume your manual count is wrong, then the formula that will give the number of dates in that column that are not in the month in the header is:

=COUNTIF(F10:F999,"<"&F9)+COUNTIF(F10:F999,">"&EOMONTH(F9,0))

Greater than 6 months relative to when? Today's date? If so, the CF rule is

=E10<EDATE(TODAY(),-D10)

It does color 89% yellow. But your display rounds the percentage so that 89.1% appears as 89%, and will not be colored because it is >89%.

The problem you are having is your definition. You are thinking of the percentages as if they are integers, but you are calculating a percentage with division so it is a real number. Your definition above does not account for the real numbers between 89 and 90, or between 50 and 51.

The simplest change you can make to fix this is to change your formula to round to the nearest whole-number percentage. You also should not be using SUM because you are not taking the sum of anything. Also multiplying and dividing by 100 has no effect.
Your formula:

=SUM(F3/F2)*100/100

Correction:

=ROUND(F3/F2,2)

I am not sending back your actual file for you but if you have any trouble with any of these suggestions let me know and I can do that. I have tested these.

Thank you Jeff, I'll work through your suggestions.

I deleted some lines so maybe the manual count was wrong...

Yes, 6 months greater than today, I think I had a =TODAY() cell hidden somewhere. But the frequencies vary is the issue, some services are every 3 months, some every 6 and some every 12....

Percentages are my bete noire... find them so difficult. Hopefully I can manage, I will shout again if not *fingers crossed*

 

[6StringJazzer]

The formula uses the column to calculate so whether it is 6, 3, 12--it will be correct.

Here's one way to think about percentages. A percentage is just a way to express one number divided by another number. The tricky part is that a percentage is expressed as something out of 100. As an example, 1/2 = 0.5. To express as a percentage, you have to multiply by 100 to get 50%, meaning 50/100.

In Excel that number would be stored as 0.5, that's the underlying value. The default display format is 0.5. If you format the display as a percentage, it will show 50% but the underlying value will still be 0.5. Same number, just two different ways to show it. So in Excel you never have to do math to convert a number to a percentage, you just change the way it's displayed.

In Excel, when you format a number as a percentage, the default is to show it rounded to the nearest whole percent. This does not change the underlying value, only what you see on the screen. So let's say we divide 507/1000 and the result is 0.507. If you format that as a percentage, it will display 51%. The underlying value is still 0.507, which is the same as 50.7%. So on the screen it looks like 51% but it's really less than that. That is what was causing your CF problem.

If you want to see more precision in your percentages you can increase the number of decimal places to display as many as you want. So in the above example, you could display 50.7% (or even 50.70000%).

Here is an example of the exact same number displayed in different ways. As you move your mouse over the numbers, you will see they are all 0.507.

$scratch.xlsm
	A	B	C	D	E
1	General (Default)	Percent	Percent with one decimal place	Percent with two decimal places	Number with only two decimal places
2	0.507	51%	50.7%	50.70%	0.51
Sheet1

 

[Bonnie Janes]

I looked at the first column to figure out what you need. Your manual number is 5 but I only counted 4 dates in that column that are not in April. Is that an error in the manual count, or am I misunderstanding the requirement? Same issue for the next three columns.

If I assume your manual count is wrong, then the formula that will give the number of dates in that column that are not in the month in the header is:

=COUNTIF(F10:F999,"<"&F9)+COUNTIF(F10:F999,">"&EOMONTH(F9,0))

Greater than 6 months relative to when? Today's date? If so, the CF rule is

=E10<EDATE(TODAY(),-D10)

It does color 89% yellow. But your display rounds the percentage so that 89.1% appears as 89%, and will not be colored because it is >89%.

The problem you are having is your definition. You are thinking of the percentages as if they are integers, but you are calculating a percentage with division so it is a real number. Your definition above does not account for the real numbers between 89 and 90, or between 50 and 51.

The simplest change you can make to fix this is to change your formula to round to the nearest whole-number percentage. You also should not be using SUM because you are not taking the sum of anything. Also multiplying and dividing by 100 has no effect.
Your formula:

=SUM(F3/F2)*100/100

Correction:

=ROUND(F3/F2,2)

I am not sending back your actual file for you but if you have any trouble with any of these suggestions let me know and I can do that. I have tested these.

Jeff - You're amazing, you've simplified everything beautifully - thank you so much, they all worked first time - bang on, you're a star! Thank you so much x

 

[6StringJazzer]

Glad to help!