Long formula in excel

[amvega]

How can I separate a formula in excel that has the IF(AND ...function on it? It should be repeated over and over until it finds the right value in the array provided in a table to bring me back a formula that includes sum, and multiplying percentages. below is the way it should look, just by taking one line of values into account.

=IF(AND(A3>C7,A3<D7),A3*F7+E7)*101.2%

The formula should continue to search for this until it finds the right value to give me the total of the formula.

Any help will be appreciated it.

Thanks!

 

[steve the fish]

Try to provide some sample data and the result you are looking for

 

[amvega]

I have the table below where I have a range of values in tons that will calculate the total due (payment) depending on the amount of tons purchased. This formula include percentages. What I need to know is using the IF(AND to use a singe formula for when the tons are purchased. in other words, that by putting the amount of tons purchased in column a, then column b will look in the table to see if it defaults in the right category to give me the right total due.

I started the formula like: =IF(AND(A3>C7,A3<d7),a3*f7+e7)*101.2% below:
<D7),A3*F7+E7)*101.2%. I need this formula to continue to search in the table below until it finds the right criteria to give me the total

[TABLE="width: 734"]
<tbody>[TR]
[TD="width: 101, bgcolor: transparent"] Production PBC [/TD]
[TD="width: 116, bgcolor: transparent"] Total Dues [/TD]
[TD="width: 759, bgcolor: #D8D8D8, colspan: 8"]AGREEGATE DUES CALCULATION[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 125,000 [/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[TD="bgcolor: #C5D9F1"]Tons From[/TD]
[TD="bgcolor: #C5D9F1"] Tons To [/TD]
[TD="bgcolor: #C5D9F1"] MIN. FEE [/TD]
[TD="bgcolor: #C5D9F1"] PERCENTAGE FEE FROM TONS [/TD]
[TD="bgcolor: #C5D9F1"] CORE CPI [/TD]
[TD="bgcolor: #C5D9F1, colspan: 3"]Amount due calculation description[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 1,873,220 [/TD]
[TD="bgcolor: transparent"] $ 10,490.49 [/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"] 59,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1,000 min. x Core CPI (1.2%) = 101.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 1,073,459 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 60,000 [/TD]
[TD="bgcolor: transparent"] 149,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.011 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .011) x Core CPI of 1.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 482,501 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 150,000 [/TD]
[TD="bgcolor: transparent"] 499,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.009 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .009) x Core CPI of 1.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 59,999 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 500,000 [/TD]
[TD="bgcolor: transparent"] 999,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.007 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .007) x Core CPI of 1.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 13,992 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 1,000,000 [/TD]
[TD="bgcolor: transparent"] 1,999,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.005 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .005) x Core CPI of 1.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 27,333 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 2,000,000 [/TD]
[TD="bgcolor: transparent"] 4,999,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.0035 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .0035) x Core CPI of 1.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 1,768,106 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 5,000,000 [/TD]
[TD="bgcolor: transparent"] 9,999,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.0025 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .0025) x Core CPI of 1.2%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 885,095 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 10,000,000 [/TD]
[TD="bgcolor: transparent"] 9,999,999,999 [/TD]
[TD="bgcolor: transparent"] 1,000 [/TD]
[TD="bgcolor: transparent"] 0.0012 [/TD]
[TD="bgcolor: transparent"] 1.0120 [/TD]
[TD="bgcolor: transparent, colspan: 3"]$1000 + (Total tons x .0012) x Core CPI of 1.2%[/TD]
[/TR]
</tbody>[/TABLE]


</d7),a3*f7+e7)*101.2%>