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