 SOLVED

# Excel formula

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

# Re: Excel formula

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.

# Re: Excel formula

@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 (Occasional Contributor)
Solution

# Re: Excel formula

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

# Re: Excel formula

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

# Re: Excel formula

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!

# Re: Excel formula

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

# Re: Excel formula

Activate the View tab of the ribbon.

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

# Re: Excel formula

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

# Re: Excel formula

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

# Re: Excel formula

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

# Re: Excel formula

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.

# Re: Excel formula

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

# Re: Excel formula

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.

# Re: Excel formula

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!

# Re: Excel formula

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

# Re: Excel formula

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?

# Re: Excel formula

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

# Re: Excel formula

In Y3:

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

In Z3:

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

# Re: Excel formula

@Hans Vogelaar your a star, thank you