ganesh6192
07-06-2017, 04:50 AM
My excel looks like this. I'm interested in getting the last value (X) in the column named MTM. This value should be equal to the sum of the last column (TV).
There are 3 below formulae
CLV=MTM*LTV
CG=20%(Total CLV)
TV=min(CLV,CG)
I could get this by trial and error but looking for a standard formulae such that the sum of TV is equal to MTM Value (X). The solver solves this in one or two iterations but I am looking for a VB code to use in macro that will solve this.
MTM
LTV
CLV
CG
TV
10000
1
10000
37435
10000
11000
0.8
8800
37435
8800
36000
0.8
28800
37435
28800
52000
0.8
51600
37435
37435
x
0.8
37435
There are 3 below formulae
CLV=MTM*LTV
CG=20%(Total CLV)
TV=min(CLV,CG)
I could get this by trial and error but looking for a standard formulae such that the sum of TV is equal to MTM Value (X). The solver solves this in one or two iterations but I am looking for a VB code to use in macro that will solve this.
MTM
LTV
CLV
CG
TV
10000
1
10000
37435
10000
11000
0.8
8800
37435
8800
36000
0.8
28800
37435
28800
52000
0.8
51600
37435
37435
x
0.8
37435