=IF(and problems

[dlatha01]

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

 

[gsistek]

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

 

[Jonmo1]

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

 

[Jeff Meyers]

Try this:

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

 

[dlatha01]

Thanks for your help everyone

Try this:

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

 

[Jeff Meyers]

Happy to help. Thank you for the feedback.