formula help - if / and statements

[ashleyfmh]

I am looking for a formula to do the following:
Agent has points for each year, 2010, 2011, 2012.
Agent can cash in those points for a trip – 1000 total points used
I want a formula to show me how many points are left for 2012 when using the oldest points first.

Ex:
2010 points 2011 points 2012 points
100 200 300
Agent uses 450 points on a trip. The 2010 points would all be used up, 2011 points would all be used, and 150 points would be used from the 2012 points.

I have used this formula and it works on some of the equations but not all.
=IF(points used=0,2012 points,(IF((points used-2010 points-2011 points-2012 points)>0,0,(-1*( points used-2010 points-2011 points-2012 points)))))

Ex: that’s not working correctly
2010 points 2011 points 2012 points
300 200 300
Agent uses 450 points on a trip. The 2010 points would all be used up, 150 of the 2011 points would be used, and 300 points would remain from the 2012 points. Using the formula above, it is showing 350.

Any advice would be greatly appreciated!

[mikeTRON]

Its probably easier to change the layout and have very simple formulas vs some formula that might be difficult for you to audit.

Also you are much more likely to get a response with a sample data set.