VBA Loop Until cell = integer
Hello All,
First time posting here but I am stuck. Normally I can tie together parts from amazing responses here to figure it out but not this time. What I want to accomplish it to reduce a cell value by 1 until an equation equals a full integer. Replacing C1 below with A1 -1 until C1 is an integer.
A1=500
B1=15
C1= cell starting at 500 (or A1) reducing by 1 until D1 = integer
D1 = ((A1/2)/B1)*100)
492 is what C1 should say and D1 should be 1640 if the loop runs correctly
I cannot get your expected result
498/2/50 = 16,6
16,6*100 = 1660
cheers
Please Login or Register to view this content.
For that to be the case, the formula in D1 would need to be
=(C1/2)/B1)*100
Anyway, the value in C1 just needs to be a multiple of 3: (1/2)*(1/15)*100 simplifies to 10/3, so every third number will meet that criteria - more precisely, a number that is a multiple of 3. Your code, however, needs to account for the floating point errors inherent to binary code: you may think that 492 is the first to meet your criteria, but 498 and 495 do that before 492.
Unless you are using weird numbers, then logic is the easiest way to solve your problem, not looping code.
Bernie Deitrick
Excel MVP 2000-2010
One might presume that A1 and B1 could change arbitrarily.Originally Posted by Bernie Deitrick
According to your formula, 498 in cell A1 gives a whole number 1660 as a result by hand calculating according to your formula in cell D1, before the value in cell A1 is reduced further more.
This result can be achieved by VBA with the following Do-Loop;
Please Login or Register to view this content.
True, but the logical requirements for any combination of linear, integer factors to produce an integer value should be relatively easy to determine.
Wait this is actually better! The closer to 500 the better!! Basically the value needs to be as close to 500 or 600 or whatever the number happens to be but the 1660 is actually $16.60 so I figured making it times 100 and checking if it was an integer was easier. Another would be A1=1600, B1=140. How would I get it to paste the result into a cell?
The numbers for A1 and B1 could be anything. THis is for quoting to make sure the value doesn't do more than two decimal places (hence the idea of multiplying and using an integer).
X * (1/2)*(1/140)*100 reduces to X * 100/280 to X * 10/28 to X * 5/14 so that will be an integer when X is a multiple of 14: The max value is INT(1600/14)*14 = 1596
Last edited by Bernie Deitrick; 04-07-2020 at 05:05 PM.
How would I have it input 1660 into a cell?
Once you have figured out how to reduce the multiplying factor to its lowest rational number, enter the denominator Z into a cell - say, C2 - and use the formula
=INT(A1/C2)*C2
or since you really want Dollars and Cents:
=INT(A1/C2)*C2/100
@Koehlr2; you can try this... (A1=500 and B1=15 or A1=1600 and B1=140 or whatever the values are...)
Please Login or Register to view this content.
Last edited by Haluk; 04-07-2020 at 05:47 PM.
Haluk, That did the trick! Thank you all for the support!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks