Creating a complex program...

Hi, new to the forums here but I am honestly at a lost and now in need of help from everyone I can think of on this situation.

I am creating a spreadsheet to calculate the annual deductions, charges, and transactions of a particulare set of data. I need to combine two complex equations and am not sure how to go about it.

Here are the two equations I am looking to combine:

1) =IF($B30="","",IF(AND($B30=0,SUM($A30)=SUM($F$7)),SUM(PRODUCT($C7,0.75)),IF(AND($B30=0,SUM($A30)=SUM($F$8)),SUM(PRODUCT($C7,0.5)),IF(AND($B30=0,SUM($A30)=SUM($F$9)),SUM(PRODUCT($C7,0.25)),IF(AND($B30=0,SUM($A30)=SUM($F$10)),SUM(PRODUCT($C7,0)),SUM($D29,$B30))))))
2)
=IF($B30="","",IF(AND($B30=0,SUM($A30)=SUM($F$28)),SUM(PRODUCT($G27,0.75)),IF(AND($B30=0,SUM($A30)=SUM($F$29)),SUM(PRODUCT($G27,0.5)),IF(AND($B30=0,SUM($A30)=SUM($F$30)),SUM(PRODUCT($G27,0.25)),IF(AND($B30=0,SUM($A30)=SUM($F$31)),SUM(PRODUCT($G27,0)),SUM($D29,$B30))))))

What this portrays is the total possibilities and outcomes of any transaction that can apply. What shows in equation one is a reference to a table that does not change, and equation two is in reference to a table that changes as transactions take place. I am willing to email this spreadsheet to those who request, but any help is good help. Thank you all in advance.

Ok i have attached my spreadsheet workbook, so here is the back ground to what it is that i am trying to get it to do; each certificate has a birth date, the day it was generated. The value of the certificate only last 5yrs before it depleets. The first two years the certificate holds its full value. Every year after that the certificate loses 25% of its original value, i.e -
if there is a value of 1.00 created in 2009
it will hold that 1.00 value untill 2011
in 2011 it will drop to 0.75
in 2012 it will drop to 0.50
in 2013 it will drop to 0.25
in 2014 it will drop to 0.00 (no more value)

each deduction is taken from the original value not its subsidary; i.e;
if there is a value of 3.86 created in 2008
it will hold that 3.86 value until 2010
in 2010 it will drop to 2.90
in 2011 it will drop to 1.93
in 2012 it will drop to 0.97
in 2013 it will drop to 0.00 (no more value)

given the event that a transaction occurs the newly deducted amount becomes the reining value: I.e;
if there is a value of 3.86 created in 2008
it will hold that 3.86 value till 2010
in 2010 it will drop to 2.90
but in 2011 before its deduction date there is a sale of 1.00
making the new amount 1.90
so in 2011 on the deduction date the new deduction will be .50% of 1.90
in 2011 it will drop to 0.95
in 2012 it will drop to 0.48
in 2013 it will drop to 0.00 (no more value)

what i am trying to create in this spreadsheet is that exact process, the only issue at hand is having the table to the right of the transaction table have a running balance that will also display its annual deductions.

This is more than likely extremely confusing and i am more than willing to answer any questions that may come up. On the bottom of the "table with sales" are three seperate equations that i have made over time, but getting them to work as one whole has not been sucessfull. If anyone can get the transaction table to keep a running balance as well as the "table with sale" to keep a running balance with the annual deductions that'd be great, otherwise i will have to live with just the way it is.

Thank you tons in advance.

Originally Posted by Paul

What are all of the SUM and PRODUCT functions doing? They're probably unnecessary. Your formula in cell D16:

=IF($B16="","",IF(AND($B16=0,SUM($A16)=SUM($F$7)),SUM(PRODUCT($D15,0.75)),IF(AND($B16=0,SUM($A16)=SUM($F$8)),SUM(PRODUCT($D15,0.5)),IF(AND($B16=0,SUM($A16)=SUM($F$9)),SUM(PRODUCT($D15,0.25)),IF(AND($B16=0,SUM($A16)=SUM($F$10)),SUM(PRODUCT($D15,0)),SUM($D15,$B16))))))

...can probably be written as:

=IF($B16="","",IF($B16=0,$D15*CHOOSE(MATCH($A16,$F$7:$F$10,0),0.75,0.5,0.25,0),$D15+$B16))

I was running though a lot of help forums and text manuals and they were recommending to add the SUM and PRODUCTS to make the equation clearer, but it shows that yours is more simplistic and works just as well thank you a lot on that one. Now if we can just solve the other 800 million problems with it, jk. LMAO

Originally Posted by JBeaucaire

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

That helped solve the length issue on my spreadsheet but did not answer the ultimate question, how can I get it to have a running balance while maintaining a running balance on the "with sale" table?