When does 55 not equal 55? 1

[jmw]

I'm using an IF function to position a number relative to other numbers which are arranged in an ascending order.
So I am saying IF a>b, b else IF a>c, b else c

Then I thought of a problem. My number might actually equal one of the numbers in my table so I wanted to create a rule for that eventuality.

This rule says something like IF:a=b,1 else 0
b is a calculation result.

Now to test this I set up data for the calculation such that I ought to get b=55 and, while some other results where coming out to 4 or 5 decimal places (general format for the cells) b came out as 55.

Unfortunately, when a= 55 and b=55 Excel insists b is greater than a and returns False.

I therefore formatted the cells as numbers with a set number of decimal places and b now returns 55.00000 and a 55.00
but I still get a false return with Excel insisting b>a

Any ideas?


JMW

http://www.ViscoAnalyser.com

 

[melone]

I guess I don't understand. I presume that your equation is:
=if(a<b,1,0)

When a=b=55, it will always take the false path in the IF statement, right? a is NOT less b (55 < 55), a = b (55 = 55). Could you just change your equation to:
=if(a<=b,1,0)

 

[brengine]

Not totally sure what you're wanting. Maybe:
=IF((A1>B1),B1,IF((A1>C1),B1,C1))
or maybe
=IF((A1=>B1),B1,IF((A1>C1),B1,C1))

Formatting the cells only changes the display, not the actual value. You need to use the ROUND function to change the looked at value. Maybe you want this:
=IF(((ROUND(A1,5))<=(ROUND(B1,2))),1,0)
...I rounded A1 to 5 places, and B1 to 2 places...which is what I think you said above.

If this is not what you're looking for, try explaining again.

Ken

 

[IRstuff]

You say that the "calculation" results in b=55. Therefore, the odds that b=a is still quite remote. The simplest test is to subtract 55 from b and see what the remainder is.

If you have some tolerance that you're willing to accept, the your IF test should be IF(abs(a-b)<TOL,X,Y)

TTFN

FAQ731-376

 

[jmw]

Thank you gentlemen for your prompt replies.

I have expanded the value of B and find that it is 54.99999999700.

This is because of the calculation errors associated with finding log.log of a number then antilog.antilog the result. So 55 goes in and 54.9999997000 comes out.

So far as I am concerned in practical terms, 54.995 should be considered as 55 (probably even 54.95) or I am going to have some problems with the subsequent calculations.
So either I round up numbers or assign tolerances as suggested by KenBolen and IRStuff.

I just wondered whether there was a way to format the cells rather than complicate the IF function.
When I can format the number in the cell and limit it to a fixed number of decimal places that only affects the displayed number.
don't see anything in options...

JMW

http://www.ViscoAnalyser.com

 

[jmw]

PS,
Melone,

If a<b or a>b I will perform a particular set of calculations but if a = b then I perform a different, simplified set of calculations.
a<=b is not an option.

As IRStuff says, the chances of a=b are pretty remote but in the real world if it happens once, it will happen at the wrong moment and the calculation will return an error and Sod's law says it will happen more than I'd like and at exactly the worst possible time so I must allow for it.

JMW

http://www.ViscoAnalyser.com

 

[brengine]

Another option is to put the ROUND as part of the function within each cell
=ROUND(antilog(log.#))

But I probably wouldn't recommend that if that number is going to be used in subsequent calculations. You'd loose some precision...altho realistically it may not even be enough to be noticed.

Or add a separate column with just the rounded values in it. That'd be easy to Copy/Paste and even change later...
B1=ROUND(A1,3)
B2=ROUND(A2,3)
B3=ROUND(A3,3)

Ken

 

[jghrist]

55 does not equal 55 when I am pulled over doing 65.

 

[IRstuff]

Excel used to have a "precision as displayed" format, but that fell by the wayside quite a while ago.

TTFN

FAQ731-376

 

[jmw]

Thanks KenBolen,
it is a simple matter for me to copy the array of answers, apply rounding to them and make decisions based on the equality of the rounded numbers (I shall round both a and b values, a being a process measurement and b is a calculation) while retaining the original values for calculations.

That precision as displayed would have been useful but the solutions offered will do the job.
Many thanks to all.

JMW

http://www.ViscoAnalyser.com

 

[StephenA]

Excel 2007 has a "Precision as displayed" function

Stephen Argles
Land & Marine

http://www.landandmarine.com

 

[IRstuff]

Oh, duh, it was there Options | Calculation | Precision as display check box

TTFN

FAQ731-376

 

[IRstuff]

But, that gets a bit tricky, since your other numbers that you have will need to be displayed at maximum display precision. Ultimately, ROUND or CEILING functions are probably more appropriate.

TTFN

FAQ731-376

 

[jmw]

Duh, Oh as well for excel 2003 but it applies to the whole workbook, not just a particular cell, which is kind of clumsy, even for Big Bill.
In the end, I didn't even need to copy the array or format the cells in any way, I simply constructed my If function as:
=IF(ROUND(H6,0)=ROUND(I6,0),1,0)
The full precision of the values is available for use in the calculations but the logic which sorts out which calculations to use compares the rounded values..... and it works very nicely.

JMW

http://www.ViscoAnalyser.com

 

[bltseattle]

I feel your pain, this is one thing in Excel that drives me buggy. I too have had IF...THENs, and VLOOKUP tables, return errant results due to the same problem (1e-8 difference in values). Use ROUND as noted above, or establish a tolerance in your IF statement such as:
IF(ABS(A-B)<=tolerance,value_if_true,value_if_false)

 

[mrainey]

It's a common problem with many programming languages when comparing floating point numbers.

Software For Metalworking

http://mrainey.freeservers.com

 

[IRstuff]

That's just something to learn as experience. Anyone familiar with how floating point numbers are represented in computers should understand that equality is a very difficult thing to determine.

TTFN

FAQ731-376

 

[BigInch]

This problem has been with us for a long time. I was calculating latitude and longitudes from offshore area coordinates when I found the most northern point returned farther south than an intermediate coordinate. I traced the error to lack of double precision of a double precision variable calling a trig function. I wrote to IBM in 1980 about it, complaining that there should be a notification somewhere in the users manual about the lack of double precision support for called functions. I received a letter from them blaming some company called Microsoft. I wrote MS and some unknown guy at the time wrote me back explaining about how it wasn't MS's fault, as they had written the program to IBM specs. Who signed that letter? Yup. Bill Gates.

http://virtualpipeline.spaces.msn.com

"We can't solve problems by using the same kind of thinking we used when we created them." -Albert Einstein

 

[chichuck]

Why not just use a lookup function?

A B C D

1
2 n = 55.3
3 lookup = 55 vlookup(c2,a6:a23,1) is the formula in cell C3
4
5
6 50 a
7 51 b
8 52 a
9 53 b
10 54 a
11 55 b
12 56 a
13 57 b
14 58 a
15 59 b
16 60 a
17 61 b
18 62 a
19 63 b
20 64 a
21 65 b
22 66 a
23 67 b
24
25
26
27 test result = calculated value not = lookup value
28
=IF(C3=C4,"calculated value = lookup vlaue","calculated value not = lookup value") is the formula in cell D27

check this structure out by substituting your calculate vale for the number in cell B2. The lookup function gives you 55 if the calculated value is 55.3, and the seme if the calculated value is 55. check for equality with the if test in cell D27.

The number in cell c3 is the number you've looked up and you can use it in calculations or for whatever purpose you desire.

Regards,

chichuck

 

[KiwiMace]

BigInch - that's an awesome story! It nearly slipped by... Post it to the "I Love Outsourced Helpdesk Personnel" forum.