LinkBack URL
About LinkBacks
I am hoping someone can help me with this. I have a slight problem and do not know how to fix and I sure hope someone has some an idea. I need to figure out stock cover days . I need to be able to count
I have attach sample file.
I am hoping someone can help me with this. I have a slight problem and do not know how to fix and I sure hope someone has some an idea. I need to figure out stock cover days . I need to be able to count
I have attach sample file.
Explain it more. What you exactly need to do.
Hi
How are YOU defining stock cover days?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
thanks for your reply
i need stock cover day on depend =opening stock +plan qty-sale target need stock cover day for example 10000 pcs/ sale target per day 50=200day cover but problem is daily target is different value please advice me
Hi,
Would you manually add the numbers you expect to see so that I can understand the goal.
In D12 and copy right,
=(SUM($C7:D7)-SUM($C10:C10)) / D10
Row\Col PRODUCTION PLAN NAME oppning stock Producd A SALE TARGTS Product A STOCK COVER DAY
Entia non sunt multiplicanda sine necessitate
58 day cover stock if sum sale number daily on base of current stock that day
I'm still not quite sure what you mean. Either shg is correct (basing value on sales target on an individual day), or this is (based on the averavge target sales per day, to date), OR you haven't explained yourself clearly enough.
=(SUM($C$7:D7)-(SUM($C$10:D10)))/AVERAGE($D$10:D10) in D12 + copy across.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 8-Jan
production plan 10 20 25 12 14 15 16 10
sale targets 10 15 25 10 11 20 10 15
oppning 50 STOCKS 50 55 55 57 60 55 61 56
stock cover day 3 3 3 4 4
dear thanks please see this table i need stock cover not on average base i need formula see the to day stock and sum daily production plan and then see the sale target where stock finsh than count the actual daycover
I don't see how you get the stock cover days, based on the example in post 9. Surely they should be as in the attached sheet?
pleas check the attach report
sale target value is diffrent on every day so average is nor work accurate result
Yea. I now know what you need, but can't see how to get there... yet.
OK. I had to do some serious hunting around to get an answer to this one. However, I'm now pretty confident that this is what you need. It is an Arry Formula, which must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of those who helped you reach a solution.
Glenn Kennedy you are a star! Thank you. I too was looking for a solution to this Excel conundrum.
any other formula with out array
I need a simple formula to calculate the cover day of stock vs rolling sales forecast with our array
As per your earlier post: why do you need a non-array formula?
And I doubt if it is possible without array formula.
And are you still on Excel 2007? If not, please update your profile with current version.
Last edited by JohnTopley; 08-23-2021 at 06:40 AM.
If you take a moving average of sales and divide that into stock levels, the results are very comparable with the current formula.
in C13 and copy across
=AVERAGE(C$7:$FH$7)
in C14
=INT(C9/C13)
copy across
Last edited by JohnTopley; 08-23-2021 at 06:52 AM.
Administrative Note:
I have merged your two threads on the same issue.
You are allowed only ONE thread per issue here.
Please see Forum Rule #5 about thread duplication.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
because array formula system runs very slow on every entry take a long time for processing that why i need
Look at post #19
What are the volumes of data (rows/columns) which I assume are related to the number of products.
Perhaps post a file which better represents your actual situation..
Non ARRAY format
In D12
=IFERROR(AGGREGATE(15,6,COLUMN($D$10:$IC$10)/(SUMIF(OFFSET(D10,,,,COLUMN($D$10:$IC$10)-COLUMN($C$10)),">0")>D11),1)-COLUMN($C$10),"NO STOCK")
Last edited by kvsrinivasamurthy; 08-23-2021 at 10:25 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
UDF "DaysCover" code given Try all options, which is faster then choose.
Code
How to Use UDF code:
Please Login or Register to view this content.
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
thanks brother
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks