SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
gjlipman
Topic Author
Posts: 1102
Joined: May 20th, 2002, 9:13 pm

roundup and match in excel

August 20th, 2007, 10:32 am

I put the following array formula into a cell, and got an #NA error. If I replace the roundup with .41, it works. =MATCH(ROUNDUP(0.403,2),{0.4;0.41;0.42},0)I think the roundup isn't giving a proper number, though I can't see what the problem is. Oddly, =MATCH(round(ROUNDUP(0.403,2),2),{0.4;0.41;0.42},0) works correctly.I'm using Excel 2002. Is this a known bug/feature?
 
User avatar
untwigged
Posts: 262
Joined: January 14th, 2006, 3:21 pm

roundup and match in excel

August 20th, 2007, 11:02 am

It will probably be just down to internal representation of floating point numbers - it fails to work in 2003 also.The round() workaround probably will have to do, or maybe you can scale your spreadsheet to use integer values.
Last edited by untwigged on August 19th, 2007, 10:00 pm, edited 1 time in total.
 
User avatar
cemil
Posts: 221
Joined: September 16th, 2005, 7:44 am

roundup and match in excel

August 20th, 2007, 11:35 am

I use Excel 2003 and i have the same problem. I think the "round" function don't round correctly.
 
User avatar
ExSan
Posts: 4554
Joined: April 12th, 2003, 10:40 am

roundup and match in excel

August 20th, 2007, 12:38 pm

I am using Excel 2000For this set of data:17, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 12, 13, 14, 15, 16the skew coeff turns out to be -3.38354E-17The "same" set of data, just ordering 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17in this case the skew coeff -6.76707E-17 ?????yes, another missed bit ? !
Last edited by ExSan on August 19th, 2007, 10:00 pm, edited 1 time in total.
 
User avatar
gjlipman
Topic Author
Posts: 1102
Joined: May 20th, 2002, 9:13 pm

roundup and match in excel

August 20th, 2007, 2:00 pm

Those sorts of problems were my first thought, and I even checked roundup(.403,2)=.41, which gave me a true - which usually isn't the case with floating point errors. That was why I thought it might be something else wrong.But thanks for the suggestions.
 
User avatar
Thinker
Posts: 53
Joined: May 2nd, 2007, 2:21 am

roundup and match in excel

August 21st, 2007, 7:48 am

This has got nothing to do with your problem, but it certainly reminds me of the ancient history of the "Pentium Problem"Here's a link I foundhttp://www.willamette.edu/~mjaneba/pentprob.html
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On