Hi
Thanks for your help...
I want to create a formula to do the following..
I want a formula that will total the production for each month (and add it onto the previous month) , subtract it from the on hand and do the following. If the on hand exceeds the total sum...then the production schedule will read 0 for that month. If the cumulative sum for that month to date is greater than on hand, then the excess of this answer over the onhand will show up....and thereafter it will the planned amount...I've been experimenting with if and offset functions but I think I'm missing something obvious..
These are my current numbers...
On hand Jan feb March April May
500 100 200 250 350 489
e.g. this is my desired result...
(planned amounts)......
On hand Jan feb March April May
500 0 0 50 350 489
Many thanks..
Anne
With these value in A1:F2
AndCode:On_hand Jan feb March April May 500 100 200 250 350 489
A3: Planned
This formula returns the planned amount:Copy that formula across through F3Code:B3: =MAX(SUM($B2:B2)-$A$2-SUM($A3:A3),0)
In the above example the Row_3 formulas return:Code:Planned 0 0 50 350 489
Is that something you can work with?
Re: Get Excel to subtract an accumulating range from a single cell and return the res
Hi
Thank you, thank you! Thank you! This works beautifully and what I love about it is the clean elegant simplicity of it...much better than the knots I was tying myself up in with if functions and offsets etc.
Best wishes
Anne
Last edited by shg; 03-16-2010 at 02:04 PM. Reason: deleted spurious quote
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks