if statement - help needed

B

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!
 
B

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

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
 
B

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!
 
J

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")))))
 
S

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trying to figure out Count feature 0
Nested IF Statement Help 4
Aging Report 7
SUMIF with date ranges 2
Nesting IF/OR Statement 14
Cumulative formula for word table 0
Aging Formula Help 4
IF/AND/OR Help 2

Top