Testing for years ending 7

[joeljoel]

I have a couple hundred years worth of data and the years are in the rows of Column B. I need to test if the year ends in 7, so 1807, 1817, etc and if so enter a -1, otherwise leave blank. I would think this should be easy, but I cannot figure it out, other than lsiting all the years ending 7 in an if statement. There must be an easier way. Thanks!

 

[kpark91]

=if(mod(b1, 10) = 7, -1, "")

or

=if(right(b1, 1) = "7", -1, "")

 

[joeljoel]

Thanks. Works like a charm. Can you teach me to fish, though?

If you could explain this a bit it would help in the future as I will likely need to look for years in multiples of 2 and multiples of 4 as well.

Thank you!

 

[learninman]

What the formula is doing is saying that the value of the cell is (If the value of the remainder of b1/10 is 7) is -1, if not then the value is "" or blank (because there is nothing between the quotes.
the second formula is if the right most letter/number is 7, then the value is -1 if not then blank

 

[joeljoel]

Thanks Learninman. I think I get it. I like the RIGHT function.

So if I understand MOD correctly, to test if a date in column B is an election year, I can write something like IF(MOD(B1,4)=0,1,"") since election years (in the US) are all divisible by 4 and even mid term election years are not?

Just tested it quickly and that works. This is fanatastic. Will make this project go so much more quickly and efficiently.

Thank you so much!!

 

[kpark91]

Glad it was of help to you.
Thank you for the feeback.