if statement - help needed

[Belinda7237]

I am trying to write an if statement that will return:

If N2 is

between 1 and 30 insert 30 into L2
between 31 and 60 insert 60 into L2
between 61 and 90 insert 90 into L2
between 91 and 120 insert 120 into L2
if greater then 121 then insert 120+ into L2

Can someone help me with the if statement for the above.

Thanks a million!

 

[Bob Phillips]

=MIN((INT((A1-1)/30)+1)*30,120)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

Try:

=IF(N2>120,"120+",CEILING(N2,30))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Belinda7237]

thanks, i realize i have my criteria wrong:

1-29 = 0
30-59 = 30
60-89 = 60
and 90 plus = 90

so the 120 at the end of the formula i can update to 90 - but how would i
change the other?

thanks again for your patience!

 

[Jennifer B]

=IF((AND((N2>1),(N2<30))),30,IF((AND((N2>31),(N2<60))),60,IF((AND((N2>61),(N2<90))),90,IF((AND((N2>91),(N2<120))),120,IF((N2>121),"120+","INVALID")))))

 

[Sandy Mann]

Try:

=MIN(FLOOR(N2,30),90)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Belinda7237]

thanks, this works perfectly! I appreciate all the help!

 

[Sandy Mann]

You are very welcome. Thank you for posting back.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk