Mar 07 2021 06:55 AM
Hi
I have a column with 66 entries of different areas of m2. How can I sort these into 5 groups, ie. under 100, 100-500, 500-1000,1000-10,000, 10,000+. Thanks
Mar 07 2021 07:02 AM
Let's say the entries begin in A2.
In another cell in row 2, enter the formula
=LOOKUP(A2,{0,100,500,1000,10000},{"under 100","100-500","500-1000","1000-10,000","10,000+"})
This can be filled down.
Mar 07 2021 08:00 AM
@Hans Vogelaar Thanks, so clever, that worked by spilling over the range each cell falls into, but gives me 67 results of each range. Can I now find out how many are in each of the 5 ranges? Thanks
Mar 07 2021 08:41 AM
SolutionYou could create a pivot table based on the data.
Add the new column to both the Rows area and the Values area.
Or use formulas such as
=COUNTIFS(A2:A67, "<100")
=COUNTIFS(A2:A67, ">=100", A2:A67, "<500")
=COUNTIFS(A2:A67, ">=500", A2:A67, "<1000")
=COUNTIFS(A2:A67, ">=1000", A2:A67, "<10000")
=COUNTIFS(A2:A67, ">=10000")
Mar 07 2021 09:43 AM
Mar 07 2021 11:00 AM
I've been using Excel for over 30 years now, so I have a lot of experience. And I learned a lot from all the talented people who help others on forums like this!
Mar 08 2021 01:08 PM
Mar 08 2021 01:42 PM
Activate the View tab of the ribbon.
In the Window group, select Freeze Panes > Freeze Top Row.
Mar 08 2021 02:06 PM
Mar 08 2021 02:08 PM
Could you attach a small sample workbook without sensitive/proprietary information?
Mar 08 2021 02:32 PM
@Hans Vogelaar I’ve sent a basic form of what I’m trying to do, so the type of garden is shown with a 1, herbicide use with a y or n.
Mar 08 2021 03:17 PM
See the attached version.
The second sheet has formulas and a chart.
I also added an alternative layout to the first sheet, and a pivot table based on this layout.
Mar 08 2021 11:41 PM
@Hans Vogelaar that’s brilliant, thanks. So it should be possible to compare any of the variables, so private that have cobbles and use herbicide? Sorry to have taken up so much of your time, I really appreciate your help!
Mar 09 2021 02:59 AM
See the attached version. You can select a variable in A2 and in B2 on Sheet2, and C2 will display the count. The result will only be meaningful for variables (columns) that contain numbers, not for columns that contain text.
If you want to be able to count the latter too, you'd need to change the layout to one that is suitable for creating a pivot table - see the example in my previous reply. Pivot tables are very flexible.
May 12 2021 08:09 AM
May 12 2021 08:39 AM
Does =MIN(A3,TIME(8,0,0)) do what you want?
Jun 17 2021 05:49 AM
Jun 17 2021 06:02 AM
Hi @Hans Vogelaar ,
Might be easier if i attach so you can see what I'm trying to do.
Im a bit of a newbie to Excel formulas, so probably making some errors somewhere.
Kind Regards,
J
Jun 17 2021 07:45 AM
Mar 07 2021 08:41 AM
SolutionYou could create a pivot table based on the data.
Add the new column to both the Rows area and the Values area.
Or use formulas such as
=COUNTIFS(A2:A67, "<100")
=COUNTIFS(A2:A67, ">=100", A2:A67, "<500")
=COUNTIFS(A2:A67, ">=500", A2:A67, "<1000")
=COUNTIFS(A2:A67, ">=1000", A2:A67, "<10000")
=COUNTIFS(A2:A67, ">=10000")