Formula that returns conditional formatting when a percentage goes through a whole integer

[JoeBuddy]

Hi everyone,

Trying to come up with a formula that compares two percentages, one from today, one from yesterday, and measures whether I have traveled through a whole percentage. I'd like it then to populate a square with something like "YES" if percentage has gone through a whole integer and NO if not.

By "whole integer" I mean 1.0%, 2.0%, 3.0% as opposed to, for instance, 1.01%, 2.34%, 3.67%.

Said another way, let's say on Monday I have 4.6% and then on Tuesday I have 7.2%, the formula would test to see if I had gone through a whole percentage (in this case I have gone through 5.0%, 6.0% and 7.0%, so YES).

The data format is pretty simple.

A1 has Monday's percentage (4.6%)
B1 has Tuesday's percentage (7.2%)
C1 has the answer (YES)

I guess I am rusty as this sounds easy but is proving to be a challenge for me.

Thanks your thoughts!

J

[FDibbins]

Hi, just so we are on the same page here, an integer is a whole number...1.0, 10.0, 100.0 etc, while a % is a decimal 0.01 (1%), 0.1 (10%) 1.1 (110%)

So you want to know if the increase is exactly multiples of 0.01 difference?

[JoeBuddy]

Thanks very much for seeking the clarification, Ford. It was a struggle to express clearly.

What I mean is, I want to test for when the figure in B1 goes through a whole number barrier, as it were.

If A1 is 4.999 and B1 is 5.000 then that would return "YES" as the amount has gone from 4.x to 5.x. If A1 is 3.0 and B1 is 5.25 then that would return "YES" as the amount has gone through the whole number barrier of 4.0 and 5.0.

Please let me know if I have expressed this lucidly.

Best,

J

[FDibbins]

So are you dealing with % or not?

Assuming not %...

	A	B	C	D
1	4	4.5	FALSE	No
2	4.5	4.5	FALSE	No
3	4	5	TRUE	Yes
4	4.5	5.5	TRUE	Yes

C1=INT(B1)-INT(A1)>0
or
D1=IF(INT(B1)-INT(A1)>0,"Yes","No")
both copied down

[JoeBuddy]

Hi Ford,

INT works. Thanks-very cool. Awesome start.

I am playing with =IF(INT(A1)-INT(B1)>0,"Yes","No")

If A1 is 5, and B1 is 4, this formula works great! Correctly displays "YES." If A1 is 5 and B1 is 5, it works, too. Correctly displays "NO."

IF A1 is 5 and B1 is 6, the formula still results in "NO" although it has traversed a whole number. I will play with it a bit.

Many thanks, again.

J

[FDibbins]

IF A1 is 5 and B1 is 6, the formula still results in "NO" although it has traversed a whole number. I will play with it a bit.

I see that you swapped the references around B1-A1 swapped to A1-B1
The reason 5-6 is saying No is because the answer is -1 which is less than 0

If you want this to ignore negative values, then wrap it in ABS...
=IF(abs(INT(A1)-INT(B1))>0,"Yes","No")

[JoeBuddy]

I see that you swapped the references around B1-A1 swapped to A1-B1
The reason 5-6 is saying No is because the answer is -1 which is less than 0

If you want this to ignore negative values, then wrap it in ABS...
=IF(abs(INT(A1)-INT(B1))>0,"Yes","No")

That is perfect, and I learned a lot here. Marking this solved.

[FDibbins]

Happy to help