I want to use an if formula that keeps subtracting monthly sales (not average) until stock cover = zero
Closing Stock Closing Stock Cover Months (not average) Jan-17 Feb-17 Mar-17 Apr-17
72,074 127 12 13,186 7,961
369 28 150 675 195
i need to know how many months would each item covers by subtracting the sales of every month with 18 months available
thank you
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Quang PT
this is the file i want to work on.
rolling forecast data is available along with stock and item codes
thank you
No data ??!!!
sorry for the inconvenience
You will either need to use "helper" columns OR VBA to calculate the cumulative monthly totals.
yes i know that, can you help me with it ?
i have tried but this is my first time using vba
=months_cover($B4,$D4:$O4)
Please Login or Register to view this content.
what if i want the formula to read from cell A1 where i include the month and to calculate based on the forecast of the next month onwards ?
i dont want the formula to stop at 12 months
maybe i add the next year forecast and want to include it in the formula, should i expand the range ?
Simply extend the highlighted range
=months_cover($B4,$D4:$O4)
in VBA change
Months_Cover = 12
to required number of months if ample stock.
If you are going to use the month in A1 then set all the dates in D3 onward to be excel dates formatted as "mmmm" (otherwise if you have more than a year's data we won't know which month/year).
This also requires changing in you require the range to be dynamic based on A1
=months_cover($B4,$D4:$O4)
so you suggest every month i update the file i adjust the range to match my needs
First, set the dates in row 3 to Excel dates for as long a period as you require (1 year, 2 years ...choice is yours).
You can then base the range to be selected by starting at the date in A1 and continuing for as many months as you require.
It is your choice as how you manage this.
thank you very much for your support and help.
i will get back in case i needed any additional info.
you got me a real solution hear
God bless you
Attached has an example:
=months_cover($B4,$O4:INDEX($D4:$O4,,MATCH($A$1,$D$3:$O$3,0)))
I would also move the Total Qty/ Total Price columns to columns D & E, so your monthly data starts in F and you then have an "infinite" range. (F4:XFD4)
Last edited by JohnTopley; 04-28-2018 at 04:23 PM.
hi, sorry to disturb again.
the formula is showing 12 when the stock is higher than rolling forecast in range, is there any solution it doesn't show like that ?
In "R4"Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Please Login or Register to view this content.
mmmm
thank you.
this would do well i guess.
i will get back if i need anything.
thank you guys for he help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks