name a formula

[daveturner80]

In our fire company we give out service pins for every 5 years of service.
I know that I can only have 7 nested formulas.
I am using the following formulas, but would like to use them as names.
=if(r3=5,5,if(r3=10,10,if(r3=15,15,if(r3=20,20,if(r3=25,25,if(r3=30,30,if(r3=35,35)))))))
=if(r3=40,40,if(r3=50,50,if(r3=55,55,if(r3=60,60,if(r3=65,65))))
<table border="0" cellpadding="0" cellspacing="0" width="80"><col width="80"><tr height="21"> <td class="xl65" style="height:15.75pt;width:60pt" height="21" width="80"> </td> </tr></table>Is there anyway I could name the top formula something like five to 35 and the second one forty to 65, and use this formula?
=if(fiveto35,fiveto35,fortyto65)
I thought I read somewhere that this could be done, or there easier formula that could be used?
Thanks for any help you can provide.
Dave
<table style="width: 80px; height: 1px;" border="0" cellpadding="0" cellspacing="0"><col width="80"><tr height="21"> <td class="xl63" style="height:15.75pt;width:60pt" height="21" width="80"> <table border="0" cellpadding="0" cellspacing="0" width="80"><col width="80"><tr height="21"> <td class="xl65" style="height:15.75pt;width:60pt" height="21" width="80">
</td> </tr></table></td> </tr></table>

 

[crook_101]

Hi Dave,

What are you trying to do with your if statements??

CHeers,
Ian

 

[crook_101]

Hi Dave,

Try the following formula, it will give you the following:

service 5 - 9 years = 5
service 10 - 14 years = 10
service 15 - 19 years = 15
etc

=LOOKUP(R3,{5,10,15,20,25,30,35,40,45,50,55,60,65},{5,10,15,20,25,30,35,40,45,50,55,60,65})

HTH
Ian

 

[daveturner80]

your lookup formula is working, however every cell that is less than 5 it is putting #N/A in the cell. How would I get these out of the cells.
Thanks for your quick response.
Dave

 

[Ron Coderre]

Does either of these formulas work for you?:

=IF(R3=CEILING(R3,5),R3,"")

or

=CEILING(R3,5)

 

[crook_101]

Hi,

Add an if statement on the front of it.....

=if(R3>4,LOOKUP(R3,{5,10,15,20,25,30,35,40,45,50,55,60,65},{5,10,15,20,25,30,35,40,45,50,55,60,65}),"Less Than 5 Years")

Ammend the formula text "less than 5 Years" as required - if you don't want anything in the cell just leave it as ""

Cheers,
Ian

 

[Armando Montes]

Try:
=FLOOR(R3,5)