I am having issue setting up an analysis page for a report that I have. I am trying to find out for each individual what are the currently working on, completed, and the average time it took to complete. I have the data but it is in the following format:
Name Started Done
Julie 24-Jun 27-Jun
Tom 24-Jun
Julie 24-Jun 27-Jun
Julie 24-Jun
Ken 24-Jun 27-Jun
The way I need the data appear with the name above with the data below as shown below.
Julie Tom
Working 1 1
Complete 2 -
Avg Time 3 -
I am having issue because the data isn't setup in a column format. Also the data list is constantly growing so I want it to be something that can update itself so I am not constantly updating the report. Please I need a excel guru to crack this case.
There may be a better way of doing this, but.
I would use concatenate and sumif to make this happen.
I set it up in the attached spreadsheet in a way that you can add to it.
The key column on the data page for completion time is a good idea since it alleviates the need for nasty array formulas, but the rest of the calculations can be done without the extra columns with normal worksheet functions.
Attached.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JB-
So awesome! I'm trying to figure out how your formula is looking at both criteria... can you give me a little hint?
I could use this for a few things that I do, as well... and it would be great if I could avoid inserting columns of concatenates
SUMPRODUCT() with the --() criteria inside converts all the TRUE/FALSE answers that each array creates into 1/0, and as you know, multiply anything by 0 and you get zero.
Basically, it's a really cool COUNTIF().
So when the two arrays of 1s and 0s created by the two criteria formulas inside the SUMPRODUCT() are multiplied against each other, only the ones that BOTH result in TRUE create the 1x1 formula.
Shorten the range down to 10 cells or so, then use the Evaluate Formula icon to watch the formula unfold one calculation at a time...watch it build the two arrays of 1s and 0s, then multiply them together, adding all the 1s left over.
Last edited by JBeaucaire; 07-08-2009 at 01:22 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Breakthrough, thanks!
I feel like I did when I first learned vlookuphow did I ever live without it
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
Last edited by JBeaucaire; 07-08-2009 at 01:27 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JB, you know that as much as I love SUMPRODUCT I would avoid where possible given performance overheads it brings with it and here given OP is seemingly running 2007 you could use COUNTIFS/SUMIFS instead which are more efficient (particularly when dealing with large volumes of data / formulae), so instead of:
B3: =SUMPRODUCT(--(Data!$A$1:$A$1000=B$2),--(Data!$C$1:$C$1000>0))
I would (in XL2007) opt for:
B3: =COUNTIFS(Data!$A$1:$A$1000,B$2,Data!$C$1:$C$1000,">0")
Obviously in cases where backwards compatability is an issue, pending volume of data, I would most likely adopt kellyfspringers suggestion of concatenation on Data and COUNTIF/SUMIF over SUMPRODUCT.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Can anybody help me.I could not find how to post in new forum
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
mishra.ananda, I moved your prior post on this thread into an entirely new thread.
Please ensure you take the time to read the Forum Rules before posting any further.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks so much for everyones help! This is awesome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks