# =IF(and problems

#### dlatha01

##### Board Regular
Hi Guys,

The below formula doesnt work. Any help much appreciated.

Thanks

=IF(OR(\$G2>3000000,\$G2<5000000), "3-5Mn", "-",IF(AND(\$G2<3000000), "<3Mn", "-",IF(AND(\$G2>10000000), ">10Mn", "-")))

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
try leaving the ,"-" only in the false portion of the last if statement.

=IF(OR(\$G2>3000000,\$G2<5000000), "3-5Mn",IF(AND(\$G2<3000000), "<3Mn",IF(AND(\$G2>10000000), ">10Mn", "-")))

Couple things.

1. OR(\$G2>3000000,\$G2<5000000)
This is ALWAYS true for any number in G2
Perhaps you mean that to be
AND(\$G2>3000000,\$G2<5000000)

2. You haven't defined all the ranges, what result is expected for a number between 5000000 and 10000000 ?

Lookup is probably the best way to go here..

Something like
=LOOKUP(\$G2,{0,3000000,5000000,10000000},{"<3","3-5","5-10",">10"})

Try this:

=IF(AND(\$G2>3000000,\$G2<5000000), "3-5Mn", IF(\$G2<3000000, "<3Mn", IF(\$G2>10000000, ">10Mn", "-")))

Try this:

=IF(AND(\$G2>3000000,\$G2<5000000), "3-5Mn", IF(\$G2<3000000, "<3Mn", IF(\$G2>10000000, ">10Mn", "-")))

Happy to help. Thank you for the feedback.

Replies
3
Views
113
Replies
4
Views
172
Replies
5
Views
601
Replies
4
Views
161
Replies
24
Views
516

1,206,808
Messages
6,074,985
Members
446,111
Latest member
trduy1908

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back