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?
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?





RE: When does 55 not equal 55?
=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?
=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?
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?
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?
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?
=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?
RE: When does 55 not equal 55?
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: When does 55 not equal 55?
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?
Stephen Argles
Land & Marine
www.landandmarine.com
RE: When does 55 not equal 55?
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: When does 55 not equal 55?
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: When does 55 not equal 55?
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?
IF(ABS(A-B)<=tolerance,value_if_true,value_if_false)
RE: When does 55 not equal 55?
Software For Metalworking
http://mrainey.freeservers.com
RE: When does 55 not equal 55?
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: When does 55 not equal 55?
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?
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?
RE: When does 55 not equal 55?
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?
It seems an typical example of buck passing and an original GIGO? Should enhance the value a bit?
JMW
www.ViscoAnalyser.com
RE: When does 55 not equal 55?
RE: When does 55 not equal 55?
JMW
www.ViscoAnalyser.com