Help with EXCEL

Chris Hogg · 2023-09-17T16:07:27+00:00

Using MS EXCEL 2007I'm trying to set up a spreadsheet to total the time spent on aproject, and hence the cost. The columns are:Col.1 Date; Col.2 Start Time hh:mm; Col.3 End Time hh:mm; Col.4 TimeSpent (End Time - Start Time), hours:minutes, hh:mm; Col.5 Time Spent,hours and fractions of an hour hh.hh (col.4 *24) see links below;Col.6 hourly rate £; Col.7 total cost of daily session (time spenthh:hh * hourly rate).An example of Row A might be :6 February 2023; 14:24; 17:38; 03:14; 3.23; 15.00; 48.50All the rows of session costs are then simply totalled to give totalcost of time spent on the project.The problem I'm having is that the total cost of each daily session isrounded to the nearest £0.25, when I want it exact. In the exampleabove, the result should be 48.45, but it displays 48.50, and as aresult the total cost is incorrect. I've tried different formats(number, currency, accounting etc.) but to no avail. How do I get it to display unrounded costs?

Sep 17, 2023 Last reply: 2 years ago 24 Replies

Rate this thread:

Tim Streater 2 years ago

Excel is fine for small things, SWMBO uses it for the accounts for the Village Hall and a couple of other entities. You can get the spreadsheet to tell you when the accounts don't balance, f'instance. But much larger and I woul dhave thought it starts to become opaque.

If you mean a 360/75, then I expect it would have had double-precision (64-bit) floating point back then. Certainly the Sigma-7 I was using in the

70s had it. I wrote a histogram package at that point, in assembler, and initially used 32-bit floating point. Testing quickly showed that this gave occasional errors, so I switched all the single-precision opcodes to their double-precision counterparts and had no more trouble.

Vote

Bob Eager 2 years ago

At some point they included decimal instructions where results could be up to 31 digits.

Vote

Tim Streater 2 years ago

That was an option, yes, but we didn't purchase it.

Vote

Paul 2 years ago

I would say "yes", except I've noted in the past how careless the staff are at LO. (Output print quality was my beef with them. They did several stupid things that defied belief.)

I would hope the person doing the engine calcs, would have at least tested the results were the same. There would not be much point in being "Excel file compatible", if your output was not compatible.

It takes people stepping in pooh like this, to build trust in a tool. Having two digits after the decimal as a display default, really is a bad idea.

formatting link

Paul

Vote

Peter Able 2 years ago

A rounding of 25p divided by 15 implies a time rounding of 1 minute. Ergo you are rounding the time to the nearest minute.

Use formatting to show a prettier result, but don't round the computations until the final result.

Vote

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required