Hi, I am trying to set up a macro in excel to automatically compute for me an answer given X dealing with thousand and non-thousand integers. Basically, I am trying to distribute the cost of yearly CO2 reductions into monthly payments. CO2 reductions are measured in 1,000lbs and cannot be sold in any units less than 1,000lbs. My question mainly concerns yearly CO2 reduction amounts that are not evenly divided by 12, like 27,000lbs. In this event, there is a 250lbs (non-thousand) remainder for each month. We would then take that 250 and multiple by 12 to get the annual amount for the remainder to tack onto the initial payment. X= yearly CO2 amount Y1= thousand monthly CO2 amount Y2= non-thousand monthly CO2 amount R= non-thousand remainder of Y2 1,000lbs CO2 = $5.95 Scenario 1. If X/12 = Y1 then monthly payments = (Y1x$5.95) Scenario 2. If X/12 = Y2 then monthly payments = ((Y2-R)x$5.95) Initial payment = ((Y2-R)x$5.95) + (12xR) So the macro would first determine whether or not the annual CO2 offset amount when divided by 12 leaves a non-thousand remainder. ie. 12,000, 24,000, 36,000, 48,000, 60,000... If the annual CO2 offset amount when divided by 12 leaves a non-thousand remainder like 27,000, then I would like to have the macro perform the operations in Scenario 2 above. Can you please help? I can be reached at hovhanh@yahoo.com

If the solution can be found with formulae, do you still want a macro? I think these formulae do what you want, but you didn't give any example answers, so I'm not sure. For 24000, these formulae will return: Monthly Payments = 11900 Initial Payment = 0 For 27000, these formulae will return: Monthly Payments = 11900 Initial Payment = 14900 Are those results right? Monthly Payment formula: =FLOOR(A1/12,1000)*5.95 Initial Payment formula: =FLOOR(A1/12,1000)*5.95+12*(A1/12-FLOOR(A1/12,1000))

Shouldn't the initial payment be this? Initial payment = ((Y2-R)x$5.95) + (12xR)*$5.95