Data is in Rows, Analysis Page in Columns

Excelpadawan · #1 07-01-2009, 09:51 PM

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.

kellyfspringer · #2 07-01-2009, 11:15 PM

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.

JBeaucaire · #3 07-01-2009, 11:46 PM

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.

kellyfspringer · #4 07-04-2009, 12:55 AM

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

JBeaucaire · #5 07-04-2009, 01:26 AM

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.

kellyfspringer · #6 07-08-2009, 12:15 AM

Breakthrough, thanks!
I feel like I did when I first learned vlookup

how did I ever live without it

JBeaucaire · #7 07-08-2009, 12:22 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

DonkeyOte · #8 07-08-2009, 02:15 AM

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.

mishra.ananda · #9 07-08-2009, 07:17 AM

Can anybody help me.I could not find how to post in new forum

DonkeyOte · #10 07-08-2009, 07:43 AM

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.

Excelpadawan · #11 07-09-2009, 08:37 PM

Thanks so much for everyones help! This is awesome

Welcome to the Excel Forum

Forum Statistics