Help with EXCEL

[Chris Hogg]

Using MS EXCEL 2007

I'm trying to set up a spreadsheet to total the time spent on a project, and hence the cost. The columns are:

Col.1 Date; Col.2 Start Time hh:mm; Col.3 End Time hh:mm; Col.4 Time Spent (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 spent hh:hh * hourly rate).

An example of Row A might be :

6 February 2023; 14:24; 17:38; 03:14; 3.23; 15.00; 48.50

All the rows of session costs are then simply totalled to give total cost of time spent on the project.

The problem I'm having is that the total cost of each daily session is rounded to the nearest £0.25, when I want it exact. In the example above, the result should be 48.45, but it displays 48.50, and as a result 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?

[Robin]

The 3.23 is rounded. The figure's actually 3.2333 (recurring). x15 give 48.50.

I might even have had the t-shirt somewhere if I weren't old enough to have earned it in Supercalc.

[Jeff Gaines]

When I wrote a program to keep maintenance accounts for a leased property I had to use "decimal" precision - which in C# meant 28 decimal places to get repeatable results. Excel's maximum is 15 decimal places:

Can you work in the highest precision until you make the final calculation?

[Paul]

[Picture]

When I tried it here, it seemed to respond to formatting the cell, to use two places after the decimal.

It likely carries the calculation to a lot more digits than that.

It would probably take some sort of ROUND() function to make it actually round and damage the calculation. For the purposes of carrying the rounding into the next calculation.

So if you had some policy that actually implemented rounding to quarters of an hour, you'd have to add that to the calc yourself. As otherwise, it's going to carry full precision, no matter what the display of the field might manage. The cell formatting allows displaying in all sorts of crazy formats.

If the function for the rounding did not exist, a wizard could add a "macro" so that a subroutine was available to do it instead, and it would then be called like a regular Excel function.

Excel can also make some fancy forms, if the right person is driving it. Our IT department at work, could make "spitting images" of tax forms, without using JPEG or anything else. They had such tight control of formatting, every detail was right. It would not surprise me to find them doing spreadsheet calcs in PowerPoint, that's the kind of wizards we had.

But what you see in my picture, is about all I can manage in a spreadsheet :-)

Paul

[Paul]

There is also a library, to assist in doing calculations where you want enhanced precision. (The 64-bit version of the library, is 70% faster than the 32-bit version. One of the few things that takes advantage of the 64-bit world when you're running programs.)

I'm not a very good programmer, but I coded a version in C and C++ just to compare speeds, and one was faster than the other (while the other "looked nicer in terms of what the source code looked like").

This can be quite slow, if you get carried away (ask for too many digits).

But for situations where you "don't want to be clever" and just brute force a problem (code it exactly as some math text describes it), it's an option. When I tried it with forty million digit numbers, it could only do about twenty calcs a second.

If they wanted to, Microsoft could use a bignum library like that and extend things.

Today, Excel uses more than one core of your processor, but it does not use an infinite number of cores. The speedup possibilities are limited, and if you insert a construct of the wrong type, it switches back to running on one core (there will be no surprises in the output). For most users (not the power users), it should use two cores. The Level 39 Wizards can easily do stuff, to make it switch to one core.

While simple spreadsheets can appear "instantaneous". it's quite easy to make a spreadsheet that takes ten minutes to settle down. In the end, it's no different than any other programming environment, in that respect.

Paul

[Chris Hogg]

Thanks for the replies. I'm getting the feeling it is just a coincidence due to rounding errors, as Robin showed, possibly coupled with multiplying all the 'times' by 15. All the final costs are either WW.00, XX.25, YY.50 or ZZ.75, and I got suspicious. Then I checked the first line (that I quoted) by hand and didn't get the same answer as the sheet. I'm still a bit surprised, but as I haven't used EXCEL in earnest for some 25 years now, and it's all a bit different, I thought it must be me doing something wrong.

[mm0fmf]

Don't do money in floating point, use BCD or a Currency type. Normally you right click the column label and select format cells and pick from in there.

[Jeff Gaines]

Nobody in the scientific community uses Excel, if they did the moon landing would have been on Jupiter :-)

[SH]

yeah, real mathematicians use Mathematica by Wolfram research and real Scientists use Matlab from Mathworks.

For those on a budget there is Mathic and Scilab....

[Chris Hogg]

Excel was all that there was from our IT dept where I worked; a successor in time to Supercalc that Robin mentioned (along with Wordstar, back in the DOS days).

[Robin]

£15/hour is 25p/minute. If you are using times rounded to the nearest your results are going to be multiples of 25p.

[Robin]

IIRC NASA did most of their sums on IBM System 75/360 machines with precision much the same as Excel. A routine skill in those days was ordering sums so as to avoid big rounding errors.

And the few scientists I was still in touch with seemed to use Matlab /with/ Excel, not in place of it.

[Jeff Layman]

Does LibreOffice Calc do the same thing?

[Andy Burns]

Floating point is Excel's only numeric type, formatting it as integer for display is too late ...

[mm0fmf]

What a crock! I never use spreadsheets but I'm stunned that something used for so much financial work has no BCD types for internal storage but only IEEE754.

[Pancho]

IEEE754 is fine, if you know what you are doing.

If you care about a few pennies, have fixed rules to round, when doing calculations that require it.

[Pancho]

Mathematica and Excel are totally different tools.

Excel is a stunning tool, I find it hard to believe anyone doing numerical work, doesn't use it.

The problem is mainly an audit, program correctness, one. So for serious reporting applications, you need rigorous testing and program validation. In these cases, Excel's ease of use stops being such an advantage.

[The Natural Philosopher]

I think they still run climate models on it :-)

[Chris Hogg]

Ah! That explains it! Many thanks.

[Spike]

LOL!