×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

When does 55 not equal 55?

When does 55 not equal 55?

When does 55 not equal 55?

(OP)
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
www.ViscoAnalyser.com

RE: When does 55 not equal 55?

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)

RE: When does 55 not equal 55?

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

RE: When does 55 not equal 55?

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: Eng-Tips.com Forum Policies

RE: When does 55 not equal 55?

(OP)
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
www.ViscoAnalyser.com

RE: When does 55 not equal 55?

(OP)
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
www.ViscoAnalyser.com

RE: When does 55 not equal 55?

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

RE: When does 55 not equal 55?

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

RE: When does 55 not equal 55?

(OP)
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
www.ViscoAnalyser.com

RE: When does 55 not equal 55?

Excel 2007 has a "Precision as displayed" function

Stephen Argles
Land & Marine
www.landandmarine.com

RE: When does 55 not equal 55?

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: Eng-Tips.com Forum Policies

RE: When does 55 not equal 55?

(OP)
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
www.ViscoAnalyser.com

RE: When does 55 not equal 55?

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)

RE: When does 55 not equal 55?

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

Software For Metalworking
http://mrainey.freeservers.com

RE: When does 55 not equal 55?

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: Eng-Tips.com Forum Policies

RE: When does 55 not equal 55?

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

RE: When does 55 not equal 55?

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

RE: When does 55 not equal 55?

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




RE: When does 55 not equal 55?

(OP)
We are in the presence of greatness.... but does an internet forum count as one of the 6 degrees of separation? can we all claim to be 3 away from Big Bill?

Actually, wasn't there one of the early Pentium maths processors that had a glitch in it somewhere and they refused to make a general recall as they said it would only affect a very few people?
Or is this it?
(I wish I had a better memory)

JMW
www.ViscoAnalyser.com

RE: When does 55 not equal 55?

(OP)
By the way, BigInch, is a letter signed by Bill Gates from 1980 as valuable as a Beatles signature? Have you had it valued?
It seems an typical example of buck passing and an original GIGO? Should enhance the value a bit?

JMW
www.ViscoAnalyser.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources