Beginner needs help ...please!!

cazfromoz

New Member
Joined
Nov 8, 2011
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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!!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
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

New Member
Joined
Nov 8, 2011
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,283
Members
414,051
Latest member
tabecker

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top