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")
Hi HansVogelaar ,
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
As always Hans, your a legend.
Sometimes I just cant see the woods for the trees!
Thank you
The formula in BT3 is incorrect - it results in a negative number.
It should be
=(E3-D3)+(U3-S3)+(AK3-AI3)+(BA3-AY3)+(BP3-BO3)
- Column BT.
The prior column shows total hours works @18, but column BT shows more hours Thanks. Which row contains an unexpected result, and why?
- Sorry Hans, relates to 'Calculations' tab, columns BT>BW
In all honesty formulas are pretty much the same, i think it may be a cell format issue i.e. time, but see what you think.
https://docs.google.com/spreadsheets/d/1P0PbyYTrepHq9O-yg8KTM21HSlo-cI9Q8uc_BeXWea4/edit?usp=sharing
I can try, but I have no experience with Google Sheets, so I can't guarantee anything.
Hey Hans hope you are well.
It was all going well, then I decided to complicate things.
Ok so i have made the spreadsheet for calculating working hour/driving time and paid hours etc. I made a google form and this now reports to a google sheet, this all works well.
However i have a glitch somewhere as the hours are being calculated oddly. It was ok on my first test sheet, but now i have developed it further the glitch has appeared, although the formulas from what i can see are correct.
If i send you a link to the Google Sheet would you be willing to have a look?
Thanks Hans, that seems to have done it 👍
Currently, Z2 displays clock time. This cannot exceed 24 hours.
Change the number format of Z2 to the custom format [h]:mm
The [ ] tell Excel to treat the hours as cumulative time instead of clock time. You'll see 33:30.
Hi Hans, hope you are well.
Almost there, however there's a slight glitch somewhere.
If you add "Yes" into V2, the sum works, however if V2 is left blank the sum in Z2 is incorrect.
Would you mind having a look, I've tried a few things but just seems to make it worse 😣
I've attached for you.
Hope you don't mind being my 'go to' guy on this?
In cell Z4 i have =SUM(X4:Y4) i have this to add a couple of cells together, however i want to add a conditional format that if say cell V4 is filled then it doubles the amount in Z4.
HansVogelaar your a star, thank you