Beginner needs help ...please!!

[cazfromoz]

Hello to all the Talanted Excel Users one of which I am not

I have extracted data from a CRM data base and would now like to group some of that data together.
I would like group data into four different groups using the one formula.

Two columns are involved with the first criteria and it needs to include the range of cells eg A1:A1699 and B1:B1699 and I am looking for a result of > 500 members(column A in spreadsheet) with an account balance or <50,000,000 (column B in spreadsheet) and would like to include an alpha or numeric value result that is either expressed as two or 2.
Second criteria is now only using the A1:A1699 column as I am looking for members >200<500 and I would like to return the value of three/3
Third criteria is >50<200 value to equal four/4
Fourth criteria is >25<50 value to equal five/5

Now this is my first post so be kind and I sincerely hope someone, anyone understands what I am trying to achieve.

Many thanks in anticipation of someones kindness!!

 

[paddydive]

Hi cazfromoz,

If i understood the requirement correctly the formula should be like below :

Please note I have mentioned numeric value as Zero (0) if non of the criteria is matched.

=IF(AND(A1>=500,B1<50000000),2,IF(AND(A1>=200,A1<=500),3,IF(AND(A1>=50,A1<200),4,IF(AND(A1>=25,A1<50),5,0))))

Hope this works for you.

Pradeep

 

[cazfromoz]

Hi Pradeep

Worked perfectly. I was on the right track when I was trying to do it myself using the if and and functions, but was unable to write the formula correctly. I will now be able to use this formula over and over just making sure the my reference cells are correct when I next use it.

Many Many Thanks.
CazfromOz