Calculation help

[steve121]

HI

im really struggling to get a formlua to work

what i want is

i have 2 varibles which i need to compare and if the figure is less than 70% tell me how many is need to make the 2nd figure 70%

eg A=12 and B= 5 so C= 41.6%

i have this so far

=(ROUNDUP((B17*H3)-C17,0))

B17= a user defined varible
H3 = 70%
C17= is a user defined varible

In this example B needs to be 70% of A

the formula i have works and tells me how many i need to make 70%

ie. if A=26 and B=15 then the answer is 4

what i want is a formula so that takes into account A and B will both increase by what ever amount the result of the formula is, so i need this to be taken into account

any ideas

 

[iliace]

Sounds like an algebra problem. Here is what you want:

B1/A1 = 0.7

However, this number is currently less. So, what you need to know is how much more B1 should be to give you 0.7, which translates to:

(B1 + x) / A1 = 0.7
B1 + x = 0.7 * A1
x = 0.7 * A1 - B1

Hence, your formula for X should be:

=IF(B1/A1>0.7,0,0.7*A1-B1)

If you want the number rounded up to the nearest whole, use:

=IF(B1/A1>0.7,0,CEILING(0.7*A1-B1,1))

or your ROUNDUP technique.

 

[mikerickson]

How about
=MAX(B1/A1,.7)

 

[steve121]

thanks for the quick reply

your formula gives me what i have already where it works out how many more B needs to increase by to by 70%+ of A

what i now wanted is

for A and B to be increase by the value of the formula result x

until the result is over 70%

as for everyone 1 more needed this will increase A and B by that amount as all 3 are linked

so at the moment if A=12 and B=5 it gives 42% and gives X a figure of 4 as a result of the formula

the problem is to get 4 more A and B would increase by that amount to A=16 and B=9 which gives 56% and x a value of 3

so im wanting it to work out how much is needed to get B to be 70% of A, but taking into acount that the figures will increase



think this may be macro teritory now

 

[mortgageman]

thanks for the quick reply

your formula gives me what i have already where it works out how many more B needs to increase by to by 70%+ of A

what i now wanted is

for A and B to be increase by the value of the formula result x

until the result is over 70%

as for everyone 1 more needed this will increase A and B by that amount as all 3 are linked

so at the moment if A=12 and B=5 it gives 42% and gives X a figure of 4 as a result of the formula

the problem is to get 4 more A and B would increase by that amount to A=16 and B=9 which gives 56% and x a value of 3

so im wanting it to work out how much is needed to get B to be 70% of A, but taking into acount that the figures will increase



think this may be macro teritory now

Either you do not understand the math solution provided by iliace or you have a different request that you stated at first. The numerator and the denomanator are not BOTH increase by x - only the numerator.

 

[barry houdini]

Let's see if I've got this right.

You want to add the same amount to both B17 and C17 so that the new amount, C17+X, is 70% [or whatever % is shown in H3] of the new amount B17+X....and round this amount X up to the next integer. If that's right try

=ROUNDUP((B17-C17)/(1-H3)-B17,0)

 

[steve121]

Barry thanks very much thats exactly what i wanted, all ive had to add now is to make a a zero if it generates a negative

=IF(ROUNDUP((B17-C17)/(1-H3)-B17,0)<0,0,(B17-C17)/(1-H3)-B17)

but thats great your a start thanks very much

 

[barry houdini]

If you never want it t be lower than zero try

=ROUNDUP(MAX(0,(B17-C17)/(1-H3)-B17),0)