lookup function

[daveturner80]

I have a spreadsheet that we use to award service pins when our members reach different years of service.
In column A we have members names and in column C we have their start date. Columns D thru Q are years where we put in a 1 for every year of service. Column R sums up Columns D thru Q. In the next column I would like to put in the members years of service and have it just put in the number of years in increments of 5 years, so if a member has 5 years it would put in a 5, but if he had 8 years the cell would just be blank. Would this be possible to do fairly easy?
Thanks for any help.
Dave

 

[barry houdini]

Would this do what you want in S2?

=IF(MOD(R2,5),"",R2)

 

[daveturner80]

it didn't seem to work.What I am getting from your code is that it is looking in row R2 and if it finds a 5, put it in the cell.
Is this correct?
If it is I have columns from A3 to A110 so would the code have to be changed to recognize these cells?
Also the years could go from 5 years all the way up to 60 in 5 year increments.
What do I need to do?
Thanks,
Dave

 

[T. Valko]

it didn't seem to work.What I am getting from your code is that it is looking in row R2 and if it finds a 5, put it in the cell.
Is this correct?
If it is I have columns from A3 to A110 so would the code have to be changed to recognize these cells?
Also the years could go from 5 years all the way up to 60 in 5 year increments.
What do I need to do?
Thanks,
Dave

The formula is testing to see if the cell contains a number that is a multiple of 5. If it is, the formula returns that value otherwise it returns a blank. For example...

R2 = 25

The formula will return 25

R2 = 16

The formula will return a blank