PDA

View Full Version : Iterative comparison in Excel using Macro



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

p45cal
07-06-2017, 08:46 AM
Is this correct:?
19684
If so a couple of formulae will suffice.
If not, what should the answer be?

p45cal
07-06-2017, 09:21 AM
52000 x .8 is not equal to 51600!
So is this right?:
19685
If so formula in C6:
=SUM(D2:D6)-SUM(C2:C5)

Formula in A6:
=C6/B6

Aussiebear
07-06-2017, 06:50 PM
Hi ganesh6192, have you forgotten something important???? Like letting us know you have posted this thread elsewhere perhaps?