How to control float-point number?

[alee001]

I found a defect after bi-section due to float-point number as follow:

Please Login or Register  to view this content.

After looping: high=1.7675; low=1.76; chk_H="L"; chk_M="L"; chk_L="S"
Range("f" & r1)=Application.Floor(low, delta)=1.75999999609…
I want to round answer to 0.01 and get Range("f" & r1)= 1.75, anyone have idea?

[alansidman]

crossposted: http://www.mrexcel.com/forum/excel-q...nt-number.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[alee001]

Sorry, I only hear more answer and advice from other forums for my question.

[joeu2004]

Sorry, I only hear more answer and advice from other forums for my question.

I sympathize. FYI, I will respond to your posting in mrexcel.com/forum. I prefer to keep all of the discussion there (one forum) in order to avoid duplication and unrebutted misdirection.

But if there other forums where you posted the question, please let me know what they all are. A google search (now; it might improve later) does not find your postings of this question in any forum, even excelforum.com and mrexcel.com/forum(!).

[MrShorty]

We might need more detail or context to understand exactly what you want.

You say that you want to "round answer to 0.01 and get Range("f" & r1)= 1.75". Your code indicates that you are using the FLOOR() function for this. The FLOOR() function should round 1.75999... down to 1.75. Are you saying that the FLOOR() function is rounding to something else? Or are you saying that it should round to 1.76, but it is erroneously rounding to 1.75?

The answer to your question is to somehow use the correct or the correct combination of Excel's/VBA's rounding functions ROUND(), Round() [note that Excel's ROUND() function behaves a little differently from VBA's Round() function], MROUND(), FLOOR(), CEILING(), ROUNDUP(), ROUNDDOWN(), etc. This usually means thinking carefully through the scenarios you expect to encounter with this algorithm and the results you want to acheive for each of those scenarios. Then study the various rounding functions, then combine them in a way that will give you the desired result. A table like this might help:

Please Login or Register  to view this content.

After thinking through and filling the desired result for different scenarios, then you can think through the available rounding functions and decide how to apply them to get the desired results.

[alee001]

I should be discussed for each of the same posts in each forum should be considered as a separate debate, unless the two forum area are related.

[joeu2004]

I should be discussed for each of the same posts in each forum should be considered as a separate debate, unless the two forum area are related.

In that case, I'll step back and not participate any of the discussions. The point is: some of us participate in both forums and others, and it is a waste of our time to have to duplicate our thoughts in responding to other comments and your questions. Good luck!

[alee001]

Using bi-section, I had tried to find the point changed near to delta 0.01 when signal state occur to L > 1.75999999609… > S, but the code output answer is 1.759999...or roundup is 1.76. I had no idea how to handle floating-point to 1.75.

[MrShorty]

I'm still not sure I understand what you are asking for. In the situation you describe (1.75999... and 1.7675), it should terminate the loop (because their difference is less than 0.01), and what value do you want returned to the spreadsheet: 1.75 or 1.76?

[alee001]

In case my range as follow list:
1.79 (L) high
1.78 (L)
1.77 (L)
1.76 (L)
...
1.74 (S)
1.73 (S) low
so the changed point to be searched by signal change from L (high=1.79) to S (low=1.73) is 1.75999...(S) near to 0.01 as 1.75 (S), but I I've never been got the point (1.75) from bi-section method due to float-point.

[alee001]

I know I made the same post in multiple forum may be cause problems, I can do better for the same in future posts indicate other forum of sources. Sorry for all inconvenient.

[MrShorty]

I still do not understand what you want.

As a last attempt to understand, I created this spreadsheet that walks through each iteration. I had to enter an initial low just less than 1.73 to get the rounding error to show up. I also had to fill in an extra entry at the top of the table. I assumed there was nothing in between the 1.74 entry and the 1.76 entry.

As I follow your code snippet (without any of the data that it is looking at), it should be returning 1.75 (cell B25, the only cell in my simulation that returns 1.75). 1.76 can easily be returned using one of the other rounding functions.

Naturally, this spreadsheet is likely much simpler than your real sheet, but does it adequately describe the problem you are having?

[alee001]

Your analysis is very detailed on worksheet, but my signal output Range("be" & r1) from all input tick point are formula. So it is difficult to know the point which signal be changed from L to S or S to L. I intend to modify code for bi-secton's process so that catch the changed point without effect by float-point as follow but still no success in some case...

Please Login or Register  to view this content.

[MrShorty]

I'm not sure I can be much more help at this point. The short (but not specific) answer to the title question is to use the different rounding functions, like you have done, until you figure out the right combination of rounding functions to overcome the floating point errors. Without being able to see the data in the spreadsheet or to understand the formulas in the spreadsheet, I'm afraid I cannot help any further.

If you were willing to mock up a spreadsheet that illustrates several cases (some that work some that don't) and upload it here, I could take a look at it and see if I can suggest something, but I don't think I can make any suggestions without understanding the details of the problem you are trying to solve.

[alee001]

Thanks for your advice and help. Due to the complexity of the different formulas and change, there are difficult to upload sample sheet for your reference.

Answer value into position after the bi-section is the key, when the signal scan (L) to the first (S), assuming that point:
Case 1: point=1.7600...(S) the answer take 1.76(S)
Case 2:point=1.76(S) 1.76(S) is the answer
Case 3: point=1.75999...(S) take 1.75(S) is the answer

In addition to using the most primitive (time-consuming) method from the beginning to the end for each scan point, I was looking for other effective or improved methods to avoid floating point find out the answer...

[MrShorty]

If I understand, the FLOOR() function should correctly perform each of those three calculations. Do you have an example where it calculates and incorrect answer?

I was looking for other effective or improved methods to avoid floating point find out the answer...

In Excel, it is difficult to completely get away from using double precision, since all numbers in Excel are stored as double precision floating point values. There are ways to avoid using double precision, but they require you to understand different data types (see VBA's available data types here: https://msdn.microsoft.com/en-us/lib.../jj692781.aspx ), their limitations, and to fully understand your calculations to know if your calculations can be accurately represented in these other data types. Double precision floating point is a very effective data type when used skillfully, so that it is usually more work than it is worth to convert a calculation to take place in a different data type.

[alee001]

I cannot find the actual sample where is incorrect point because there are over 4K rows data are searched in range per row on my project. But this problem (3 cases ) does exist, so I only find out (maybe) problem data rows to re-check them. Anyway Thanks you for your reference and help. I'll learn and study more.

After re-modified code, tests did not reveal any problems occur...

Please Login or Register  to view this content.

[alee001]

I found the answer is incorrect(1.76) if using Dim delta as Single as follow code:

Please Login or Register  to view this content.

[MrShorty]

Interesting. If you have never done so, you might look at what happens when you convert from single to double. Something like

Please Login or Register  to view this content.

You should easily see that 0.01 (single) converted to double is no longer 0.01 (I get 9.9999...E-3). I expect that, because Excel only uses Double as a data type, all of Excel's worksheet functions (including FLOOR() or FLOOR.PRECISE()) are designed to receive doubles, so singles will be converted to doubles when calling these functions.

I personally never (or rarely) use single data, because single is much more prone to floating point errors, like you are seeing here. If you do choose to use single, then you, the programmer, need to be aware of what can happen when converting from single to double and write the program accordingly.

[alee001]

This is how I started the wrong reasons, I have to pay more attention to. Thank you for your advice once again.