Problem with Iterative Code, Excel Precision, or my maths??

**Phil_V** · 05-15-2009, 08:13 AM

Ok, I am not overly sure here where the problem may lay.

The task I have is this.

I have a number of houses & a number of water plants (as in water processing plants, not the green kind!)
Each water plant can provide a certain % water coverage to all properties.
'All Properties' is classed as the number of houses + the number of water plants.

The 'coverage' as a percentage is given by the formula:

Please Login or Register  to view this content.

My problem is that I want to be able to work out how many Water Plants are needed to provide a certain coverage (80%) to the properties that I have.
The 'hiccup' here of course is that once you add those Water Plants, then the number of properties will increase by that amount, which may in turn mean that you need more Water Plants

So, the task that I have done, is to take the original formula and translate it so that it gives me 'Water Plants' based on a coverage of 80:

Please Login or Register  to view this content.

I've then wrapped this up in an interative loop.

The problem that I have is that if I feed my original formula with the amount of water plants that my new formula says I would need for 80% coverage, it always seems to come out at 81 or something around 80, but NOT 80. (It doesn't have to be exactly 80, it can be 79.xxxxxxxx or 80.xxxxxxx, and as long as it is that consitently I will use roundup, or rounddown).

I don't see why I am not getting back to my original figure of 80

Perhaps someone can take a look and just check if the formula translation, and the resulting code are good, and if so, perhaps see what is going on?

I've attached an example workbook. To run the macro just change the number of 'Houses' in B1. This will give a figure in B7. Feeding that figure back into B2 (as an integer) SHOULD make B6 and C6 equal 80(ish) (but it doesn't).

B6 is calculated using spreadsheet formula, and C6 is calculated using VBA formula just to check that there were no differences between the 2.

Many thanks

NB: Sub Do_Events() is there purely as an assistance in case you abort the macro when Application.EnableEvents=FALSE.

**Phil_V** · 05-18-2009, 03:51 AM

Just giving it a quick bump with fingers crossed to see if anyone can help

**shg** · 05-18-2009, 10:29 AM

See attached.

The solution is just = k * numHouses, where k = 1 / (14^(2 - coverage) - 5)

For 80% coverage, k ~ 0.05338 = 1 plant for every ~18.73 houses

**shg** · 05-18-2009, 06:24 PM

Phil,

Here's the equation as a WMF that you couldn't read it in the workbook.

It was created in MathType (http://www.dessci.com/en/products/mathtype/), the big brother of Office's Equation Editor.

**Phil_V** · 05-19-2009, 04:34 AM

Many thanks SHG.
In both the WMF and the Excel document the characters are corrupted

Looking at the spreadsheet formula that is in the cell it looks like you have used a different equation to work out not only the plants needed, but also the 'Coverage' to the one I supplied.
I would really like to know how you derived the new formula. Can MathType perhaps print directly to a PDF that you could attach?

Thanks again

EDIT

Don't worry, I found out that I needed to download the MathType fonts.

Now that I have them I am still confused how / why you changed the start of the 'Coverage' formula?

Mine started with;

C = 200-100*(.....

However yours is starting with;

C = 2-(.....

Please can you explain the reasoning behind changing this start of the equation? - Thanks

**shg** · 05-19-2009, 09:59 AM

Your formula computes "percent," as in 80 = 80%. But percent means, literally, 'per hundred', so 80% := 80/100 = 0.8.

People who think you divide a percentage by 100 to get a decimal are right only in the mechanical sense; 80% is 0.8 without any division whatsoever. As you'd say in Excelese, the difference is just formatting.

So the answer is, I scaled the equation by a factor of 100.