# Calculation help

#### steve121

##### New Member
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### iliace

##### Well-known Member
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))

=MAX(B1/A1,.7)

#### steve121

##### New Member

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

##### Well-known Member

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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
If you never want it t be lower than zero try

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

Replies
3
Views
171
Replies
1
Views
93
Replies
3
Views
86
Replies
5
Views
130
Replies
3
Views
232

1,191,499
Messages
5,986,919
Members
440,065
Latest member
JCH136

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back