Junk

Ask something
Question Locked

IFs & AND formulae

0 helpful
8 replies

by delboy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can you please tell me how I group the following:

=IF(AND(B3>=100,B3<=150),"15",""),(B3>=150,B3<=200),20",""),(B3>=200,B3<=250),25",""),(B3>=250,B3<=300),30",""))

The formulae will eventually look like this:

If (B3 = 100 to 150 then put 15
B3 = 150 to 200 then put 20
B3 = 200 to 250 then put 25
B3 = 250 to 300 then put 30 else leave blank)

Thanx for your help

8 replies

by locolobo

You need an IF for each condition. For example:

=IF(AND(B3>=100,B3<=150),"15",IF(AND(B3>=150,B3<=200),20, ...

The IF statements are nested. The second IF statement is the else clause of the first IF and so on. At the end you will have the "leave blank" statement:

... IF(AND(B3>=250,B3<=300),30,"")))

Note: I didn't check the closing parens. You must make sure they match!

Post back and let us know how you are doing. Or if you need more help.

by bizzo

=IF(AND(B3>=100,B3<150),15,IF(AND(B3>=150,B3<200),20,IF(AND(B3>=200,B3<250),25,IF(AND(B3>=250,B3<300),30,""))))

Edit: You will need to check the boundaries (100, 150, ..., 300) of your groups, as in your example, 150 will return either 10 or 15, depending how it's coded.

In my statement, I've only included the lower boundary in the group eg. B3<=100, B3<150. So 100 returns 15, 150 returns 20, ... 300 return blank.

by Member 25348569

=IF(B1<100,"",IF(B1<151,15,IF(B1<201,20,IF(B1<251,25,IF(B1<301,30,"")))))

Neil :)

by delboy

Sorry I kept comming back to this site but didnt realise you guys had posted replies (was not familliar with how to see the replies).
I will try your ideas when i get time, but thanx a million for all your responses.
Best Regards. Del

by delboy

Have now read and implemented your explanations.... works for me.
Thanx again for your help.
Kind Regards.
Del (UK)

This discussion is closed to new replies.