Calculation

[Thomas Kuriakose]

Respected Sirs,

I m not able to get this calculation working, my apologies for asking this.

We need do a reverse calculation as mentioned in F15 and F16 of the attached file.

Kindly guide me on this.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

I am not clear on what you want to do.

There are already calculations in column C , which give an output of 20,543.64 in C15. However , this results in a value of 1.529091% in C16.

So now , if we do the reverse calculation , entering 1.53% in C16 , we will get an output of 20,555.86 in C15.

So how exactly do you want a value of 1.53% to result in an output of 20,543.64 ?

Narayan

 

[bosco_yip]

Maybe,

E16 =1.53%

E15 =C12/(100%-E16-(C16-E16))-C12 =20,543.64

Regards
Bosco

 

[NARAYANK991]

Maybe,

E16 =1.53%

E15 =C12/(100%-E16-(C16-E16))-C12 =20,543.64

Regards
Bosco

Hi Bosco ,

Given your formula in E15 , it can be simplified by removing the brackets , to give :

E15 = C12/(1 - E16 - C16 + E16) - C12

which reduces to :

E15 = C12/(1 - C16) - C12

which means the formula does not use E16 at all.

It makes use of C16 , and C16 because of the formula in it , is 1.529091% , not 1.53% as displayed.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for your support on this question.

So how exactly do you want a value of 1.53% to result in an output of 20,543.64 ? -

1. Sir, the value 1.529091% you mentioned is correct, I rounded up to 1.53%. Apologies for this.

2. We need to take the values in C4, C12 and C19 cells to arrive at 20,543.64. This is required in changing E15 and see the impact on C4 and C15, the values in C19 and C12 are fixed values and cannot be changed. Based on value in E15, C4 and C15 should change.

Thank you very much for this support once again.

with regards,
thomas

 

[bosco_yip]

Hi Bosco ,

Given your formula in E15 , it can be simplified by removing the brackets , to give :

E15 = C12/(1 - E16 - C16 + E16) - C12

which reduces to :

E15 = C12/(1 - C16) - C12

which means the formula does not use E16 at all.

It makes use of C16 , and C16 because of the formula in it , is 1.529091% , not 1.53% as displayed.

Narayan

Hi Narayan,

1] I know already =C12/(100%-E16-(C16-E16))-C12

is equal to =C12/(100%-C16)-C12

2] It is because OP specified that : "We will input value 1.53% here in E16", thus the formula must include E16 inside.

Which is the actual working requirement--> complied with the specification.

Regards
Bosco

 

[NARAYANK991]

Hi Thomas ,

You have mentioned :

We need to take the values in C4, C12 and C19 cells to arrive at 20,543.64. This is required in changing E15 and see the impact on C4 and C15, the values in C19 and C12 are fixed values and cannot be changed. Based on value in E15, C4 and C15 should change.

C4 - manually entered data
C12 - has a formula =SUM(C8:C11)
C19 - has a formula =C5 - C4
C5 - manually entered data
C15 - has a formula =C4 - C12 + C19 , which reduces to C4 - C12 + C5 - C4 , which further reduces to C5 - C12

Now , if we wish to maintain C12 and C19 , as you have said , it means that the difference between C5 and C4 has to be maintained. Thus , if C4 changes , C5 will also have to change to maintain their difference constant.

Thus , to change C4 and / or C15 , one can only change C5 and C4 , or any of C8 through C11.

Now , you have the formula :

=C12/(100% - 1.53%) - C12

in K15 , which means only a change in C12 will change K15.

How do you reconcile these two ? On one hand , you want that C12 should not be changed , and on the other , only a change in C12 will change K15.

Can you clarify ?

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the detailed inputs and your support.

Sir, I reworked on the file and after struggling for hours tried to get some result but this is not correct. Now if we change the percentage in C16,this is based on C5, the values in C15,C5 and C4 changes. This has resulted in circular reference. I enabled iterative calculation.

In column E is the desired results based on one example input of 1.53% in C16.

Kindly find attached the file for your perusal,

thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

I think first you should segregate all data which is invariable , from that which can be varied.

Put all the invariable data in a different area of the worksheet , and put only the variable data in the first few columns and rows.

Your latest uploaded workbook does not make things clear.

If C15 should be 20,543.64 , then this value should be in the formula in C5 , which will then become :

=C12+20543.64

Doing this changes the value in C15 to 20,875.25

You should first list out all the calculations in plain English ; translating this logic to Excel formulae can come later.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Apologies for the clumsy data in the previous file.

Kindly find attached the revised file with the variable, fixed and change values.

Thank you very much for your support,

with regards,
thomas

 

[NARAYANK991]

Hi ,

I have mentioned this earlier , and your latest uploaded workbook confirms this.

1. TPC and PM are fixed , cannot be changed.

2. Ensure that PN and NT are the values that they should be.

3. C now gets the value 20,875.25

How can we now ensure that C attains the value 20,543.64 ? Isn't it impossible ?

You need to relook at your constraints.

Narayan

 

[bosco_yip]

Here is my breakdown and attachment.

1] Dropdown In D8, select 1.5300000% or 1.529097%

If D8 cell format set to % with 2 decimal places, cell will show in same % >> 1.53%

If D8 cell format set to % with 6 decimal places, cell will show in difference value >> 1.5300000% or 1.529097%

2] Formulas given in D2, D3 and D7 will change in accordingly

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this insight and solution.

Much appreciated,

with regards,
thomas