SOLVED

Excel formula

Copper Contributor

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

32 Replies

@StevieBrett 

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.

@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

best response confirmed by StevieBrett (Copper Contributor)
Solution

@StevieBrett 

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

You’re a genius, that worked a treat, thank you. Tired eyes now but I may well have a few questions about comparing data later. Where did you learn all this? Thanks, your help is much appreciated

@StevieBrett 

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!

Hi Hans, you’re my go to guru now! I have a spreadsheet open, columns go up to BE, I have at least 70 entries for the rows. Apart from reducing the viewing size (now 42 %) how can I see the column headings in row 1 to keep entering the data in the lower rows (hope that makes sense!)

@StevieBrett 

Activate the View tab of the ribbon.

In the Window group, select Freeze Panes > Freeze Top Row.

Wow, easy as that! But that was just a warm up. So this spreadsheet is results from a survey. To compare different parts....so columns b,c,d,e,f are types of gardens (private, charity etc.), column AE is do they use herbicide (Y or N) How do I work out which how many of each garden type uses herbicide (Y) or doesn’t (N). I guess I just use this formulae then for any other comparisons throughout the sheet to generate graphs? Thanks

@StevieBrett 

Could you attach a small sample workbook without sensitive/proprietary information?

@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. 

@StevieBrett 

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.

@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!

@StevieBrett 

 

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.

I'm trying to do something really simple, but seem to be struggling.

Working on a timesheet template.

A1=Start Time 08:00, A2=Finish Time 17:00, A3 = Hours Worked 09:00.

In cell A4 I am trying to show standard hours <=08:00, however all of the formulas I seem to try come up with errors or do not work, or it just comes up with a count i.e. 1, not the hours.

Tried COUNTIF and various others.

Help! 

@Jim_Currier__NPL 

Does =MIN(A3,TIME(8,0,0)) do what you want?

Hi Hans,
No this comes back with a 0 value.
I've, tried =MIN(SUM(W3-X3),8) also, but this comes back with a figure greater than 8.
Is it because the cell is formatted to hh:mm, and the formula doesn't recognise this?

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

@Jim_Currier__NPL 

In Y3:

 

=MIN(W3-X3,TIME(8,0,0))

 

In Z3:

 

=MAX(W3-X3-TIME(8,0,0),0)

@Hans Vogelaar your a star, thank you

1 best response

Accepted Solutions
best response confirmed by StevieBrett (Copper Contributor)
Solution

@StevieBrett 

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

View solution in original post