• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Decimal Accuracy (and Conditional Formatting)

SaviourV

New Member
Greetings, everyone.

Just need a bit of help on this particular conundrum. I doubt it has been posted up before, but correct me if I'm wrong.

A little background about the image: I'm a member of a Paid-To-Click site, and I made this spreadsheet to calculate how much I need to obtain to get certain cash bonuses. The values on the left-most column are the bonus amounts (in USD), while the percentages are represented by the first row's values.

My aim was to figure out how much I would need to get to qualify for a certain bonus amount, which is calculated as (bonus amount / percentage = how much I need). For example: I would need to make USD 0.40 to get a bonus of USD 0.02, based on a 5% percentage, but if the percentage were raised to 8%, I'd only need USD 0.25 to get the same amount.

If you look at the attached image, you'll notice that some of the values are highlighted in green. That was done by a few uses of Format Painter.

My questions, therefore, are as follows:

1. Is it possible to apply conditional formatting to highlight cells that fit the criteria of exact decimal values, up to 3 decimal places, at most? That would mean that anything that 0.5, 0.25, and 0.125 are acceptable, since they've been divided evenly, but anything that doesn't divide evenly (anything beyond 3 decimal places) shouldn't get highlighted at all. More importantly, how can I do it?

2. Does Excel have any functions or operators related to decimal accuracy as opposed to decimal precision? To my limited knowledge, the ROUND() function rounds a number to the nearest digit, but it only makes it precise, NOT accurate.

I mean, you can round 0.121428571 to 3 decimal places, which would show it as 0.121, but 0.121 is NOT an accurate figure, so you could expect some deviation of value when you use that rounded number in calculations, right?

Sorry if this sounds strange, but I'm a bit of a stickler for perfection when it comes to numbers, sometimes. The mere sight of 22/7 (yes, "pi") makes me cringe internally when I notice the decimals that follow after 3.142, and it just makes me wish there was an absolute value.

Thanks for the help, by the way.
 

Attachments

  • Problem.png
    Problem.png
    50 KB · Views: 7
Hi ,

You can check using the formula :

=ROUND(A1,3)=A1

where A1 contains your decimal number.

If this is TRUE , it means that there are no digits beyond the third decimal place.

Narayan
 
NARAYANK991, thanks! That worked out fine, although I did have to change the formula to cater for 4 decimal places instead of one. It's how that particular PTC site operates, by the way.

That aside, there's still the second question. It seems that the only way is to use precision-based functions, such as ROUND() in order to get accuracy, if your formula is a sign of things.

I guess that Excel isn't exactly programmed for accuracy, although I could be wrong.
 
@SaviourV,

This doesn't really answer your question, but perhaps it will help you make some decisions about your model. Excel stores the currency data type in memory as a long integer representing the entered value * 10,000 (i.e., retaining 4 decimal places).

Any software, no matter how well written, will be subject to the constraints of the hardware. There will always be some numeric values/calculations that will require rounding or some other truncation. The question that you will have to answer is what you will consider acceptable for your model.

Hope that helps.

Regards,
Ken
 
Thanks, KenU.

I believe the second paragraph's an acceptable answer to the second question. I guess that the word "accuracy" is not so easy to achieve after all; like you said, hardware constraints dictate things.

That, and in hindsight, not even the metric or the Imperial measurement systems are 100% accurate, am I right?

I guess we humans will never find true accuracy when it comes to our calculations, after all. Our so-called 100% may really be just 99.999999999993% when it really comes down to it.
 
I guess that the word "accuracy" is not so easy to achieve after all

But to what level of accuracy, I have spent my working life in engineering and accuracy can be achieved, it all comes down to what you want, if you are working in millimeters, but you keep using the micrometer scale and measurement then you are achieving over the required accuracy, similarly if you have the decimal places set to six, but only use two then it is over egging it.


Our so-called 100% may really be just 99.999999999993% when it really comes down to it.

Then it is not one hundred percent.

It is the same as those who say they will give 100% to work or a project, impossible, if someone gives 75 to 80% then you are getting a more accurate and truthful commitment of the person. Those who say they will give 110% have no idea of percentages and availability, do not employ them.



.
 
Thanks, pecoflyer and bobhc! I think the second question's raised quite a few issues that I didn't know about until now.

bobhc, I think that a lot of us tend to overuse the phrase "I'll give 110% to performing this job," myself included, sometimes. It gives the impression that we're mindless zombies, dedicated to nothing BUT work.

I'm currently re-working that worksheet, by the way. Turns out there was something I missed about it. Some of the duplicate values I was expecting to highlight (with Conditional Formatting) did NOT get highlighted. Still trying to get it right.

pecoflyer, the first paragraph of the link you sent made me cringe internally; 0.1 gets represented perfectly in human terms, but pass that to a computer and you can expect the resulting stored number to look like 22/7 (with digits that go on almost infinitely...o_O ).

I'll let you know when I've managed to solve this, somehow. Or if I need a few more hints.

Anyway, thanks for your input on the whole thing, everyone!
 
Back
Top