Steve VanderBeek

Alright lets see what you guys can come up with.

I've got 4 columns. First is GCI (Gross amount made from transaction in this case). Second is a percentage based deduction by the company we work for so they can pay their salaried employees (30%) and is displayed as a countdown from it's cap of $15,000. Third is a deduction for using the company's trademark, name, ect (6%) and is displayed as a countdown from it's cap of $3,000. Which brings me to the 4th column and my problem. The fourth column is calculating the running total for the Net income (gross minus deductions). Presently I have it setup with this formula. =IF(G6>=0,IF(H6>=0,F6+I5,F6-(F6*0.06)+I5),IF(H6>=0,F6-(F6*0.3)+I5,I5+F6-(F6*0.3)-(F6*0.06))) In English this means check column 2 and see if it's reached a positive number yet (meaning the cap was reached) if so then check column 3 and see if it's cap has been reached, if so then take column 1 plus the calculated net from the row above and add them together, if not then take column 1 minus the deduction for column 3 only and add it to the net from the row above, Otherwise if column 2 is not above 0 check column 3 and is if it's above 0, if it is then take column 1 minus the deduction from column 2 and add it to the net from the row above, otherwise (both being below 0) take column 1 and add it to the net above subtracting the percentages for column 2 and 3.

That mess of logic does 95% of what I need it to do. The problem comes in where income in column 1 not only makes column 2 and 3 reach 0 but makes them go positive. In otherwords part of the income has a deduction but the other part doesn't. I need a change to the logic check so that if it is above 0 it can calculate how much of the income in column 1 was and wasn't needed to be deducted so that the net income reflects correctly. Otherwise the net will reflect too much/too little.

Side notes. Column 1 is not a running total and only reflects gross income from the transaction. Column 2 and 3 are count downs (or count ups since it's going from a negative number toward 0), Column 4 is a running total of net income. The displays on these columns cannot be changed in purpose. Otherwise that mess of code would be a lot more compact. Anyways any ideas?

I need to know how I could have the following formula work so that whatever is in A1, when divided by 1000, doesn't include anything below a whole number for use in the rest of the equation.

For example if A1 = 122,900 it would show 122.9 when divided by 1000 but I want it to just be 122 for use in the rest of the formula. Anyone know how to do that?

Here's the formula: =(A1/1000*3+50)/2