I am a little stuck on this one and hopefully somebody will be able to help me out. I have a large data file that contains the usage for all my parts. I am looking to average their usage so I can plan my purchasing accordingly. What I am hoping for is a formula that will count the number of months between the first issue of a part and today then divide the total usage by that number.
I have attached a sample of of my data.
Thanks in advance.
See the attached file, with an pivot table.
Maybe this is what you're looking for.
Please reply.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
if your table sorted by month from oldest to newest: =DATEDIF(INDEX($A$2:$A$11,MATCH(A14,$B$2:$B$11,0)),TODAY(),"M")
if your dates in table are mixed: =DATEDIF(INDEX($A$2:$A$11,MATCH(A14,$B$2:$B$11,0)),TODAY(),"M") Ctrl+Shift+enter
Appreciate the help? CLICK *
Or this in cell C14 and copy down:
=SUMIF($B$2:$B$11,A14,$C$2:$C$11)/DATEDIF(INDEX($A$2:$A$11,MATCH(A14,$B$2:$B$11,0)),TODAY(),"m")
Non array formula approach.
Last edited by BadlySpelledBuoy; 08-17-2013 at 03:45 AM.
Or another approach using an array formula...
=SUMIF($B$2:$B$11,A14,$C$2:$C$11)/DATEDIF(MIN(IF($B$2:$B$11=A14,$A$2:$A$11,99999)),TODAY(),"m")
...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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you all for the quick responses. I will give them a try and reply back when i am done.
I ended up emplementing BadlySpelledBuoy's solution. It was exactly wat i was looking for without the need of an array. thanks again everybody for your input.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks