Forum Discussion
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")
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.
HansVogelaar 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
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")
- 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!Does =MIN(A3,TIME(8,0,0)) do what you want?
- 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
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!
