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!

=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)

"Belinda7237" wrote in message
...
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!

Try:

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

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Belinda7237" wrote in message
...
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!

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!

"Bob Phillips" wrote:

=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)

"Belinda7237" wrote in message
...
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!

=IF((AND((N21),(N2<30))),30,IF((AND((N231),(N2<6 0))),60,IF((AND((N261),(N2<90))),90,IF((AND((N29 1),(N2<120))),120,IF((N2121),"120+","INVALID")))) )

"Belinda7237" wrote:

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!

Try:

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

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Belinda7237" wrote in message
...
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!

"Bob Phillips" wrote:

=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)

"Belinda7237" wrote in message
...
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!

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

"Sandy Mann" wrote:

Try:

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

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Belinda7237" wrote in message
...
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!

"Bob Phillips" wrote:

=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)

"Belinda7237" wrote in message
...
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!

You are very welcome. Thank you for posting back.

--

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


Replace @mailinator.com with @tiscali.co.uk


"Belinda7237" wrote in message
...
thanks, this works perfectly! I appreciate all the help!

"Sandy Mann" wrote:

Try:

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

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Belinda7237" wrote in message
...
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!

"Bob Phillips" wrote:

=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)

"Belinda7237" wrote in message
...
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!