Hey,
I donīt know how to calculate the average demand intervall (ADI) for a time series in Excel. Perhaps you can help me to solve the problem. In the attached file there is a time series for intermittent demand. The calculation for the green cells is necessary. I donīt get it. Perhaps it is also possible to calculate the ADI in only one cell without the intermediate step.
Thank you so much.
This formula in cell P1 gives the result you want.
=N2/COUNTIF(B2:M2,"=0")
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
Thatīs right. Thank you. Can you also show me, how I get to the result in N2?
Based on http://tesi.cab.unipd.it/25014/1/Tes...garo580457.pdf, row 3 doesn't look correct for the data in row 2.
Row\Col Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Avg Inter-Demand Interval Amount N2: {=SUM(FREQUENCY(IF(B2:M2=0, COLUMN(B2:M2)), IF(B2:M2<>0, COLUMN(B2:M2)))) / SUMPRODUCT(--(FREQUENCY(IF(B2:M2=0, COLUMN(B2:M2)), IF(B2:M2<>0, COLUMN(B2:M2)))=0))} intervals
Entia non sunt multiplicanda sine necessitate
Hey,
thatīs it! Wow. Thank you. But I have uploaded a sheet, where it comes to a division through zero. Therefore there is no result. Do you see a chance to fix this?
Bye
My bad.
Row\Col Prod 2011Q1 2011Q2 2011Q3 2011Q4 2012Q1 2012Q2 2012Q3 2012Q4 2013Q1 2013Q2 2013Q3 2013Q4 2014Q1 2014Q2 2014Q3 2014Q4 A B C D E F G S Z
The array formula in R3 is
The reason for the #DIV/0! errors is that there are no non-demand periods for those products.
Please Login or Register to view this content.
Last edited by shg; 02-10-2015 at 12:51 PM.
Hi,
sorry for late answering. I have tried to understand what the expressions mean
The first part of the calculationg about the non-zero demand is surely right, but I think there is a misunderstanding about the calculation in the second part. The source you mentioned and also the calculation in the following links give another solution as your solution above.
http://www.supplychainfocus.com/foru...-interval.html
http://www.supplychainfocus.com/foru...-interval.html
I have attached a new file, with the final results. The green area is already right now, but the red one is missing. Can you look at this one for a second time. Perhaps you have the right formula to get the right solution. It would be nice, when the "ADI" is calculated in only one field, too. Thanks a lot.
Bye
Hi,
Please, how to calculate ADI in excel sheet.
Thanks!
Last edited by Adiaba; 12-18-2018 at 06:19 AM.
Unfortunately your post does not comply with Rule 4 of our Forum RULES. 4. Do not post a new help request in an existing thread. (B)
Please start your own thread. If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks