Help setting up macros

May 12, 2009 at 11:31:35
Specs: Windows XP
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



See More: Help setting up macros

Report •


#1
May 12, 2009 at 12:37:51
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))


Report •

#2
May 12, 2009 at 13:03:00
Shouldn't the initial payment be this?

Initial payment = ((Y2-R)x$5.95) + (12xR)*$5.95


Report •

Related Solutions


Ask Question