Excel array formula, offset, index, match...

[flippertie]

Hi, I'm trying to separate several datasets from a long table so I can graph the results.

Sample spreadsheet attached

Data is in a table containing a name, date and value

A; B; C;
Cris; 01-Oct-10; 52
Dave; 03-Oct-10; 151
Ella; 03-Oct-10; 65
Fred; 08-Oct-10; 98
Bill; 08-Oct-10; 117
Cris; 08-Oct-10; 62
Cris; 15-Oct-10; 54
Adam; 15-Oct-10; 121


I have used array formulas to separate out the unique dates and names for the axes of
the table that will be used for the graph: eg


.......... Adam; ...Bill; ...Cris; ...Dave; ...Ella; ...Fred
01-Oct-10 .................. 52
03-Oct-10 .............................151 ....65
08-Oct-10 etc etc.... 117 62 98
15-Oct-10 121

The bit I'm struggling with is the array formula to populate the table. It needs to reference the date in the left col and the name above and fill in the corresponding cells.

I expect to use index, match, offset - but I just cant get it to work :-/

[flippertie]

excuse the ........ above - just a vain attempt to align things in columns

[NBVC]

Does this work for you?

In G22:

=SUMPRODUCT(--(INT(PlayedDates)=$E22),--(UserNames=G$21),Rating)

copied across the matrix.

[flippertie]

Man you're good. And fast! Thank you!

I copied the formula across the matrix (as a standard formula, not Ctrl-Shft-Enter) and it works. I can add an IF(..) to get rid of the zero values.

What I've discovered is that SUNPRODUCT is summing the products (surprise!), which means that if I get two results in for the same person on the same day the score is added together which would put a spike in the graph (eg Adam gets 256 on Oct 18)

Is there a way to pick only the first, or the highest value if a person has two records on the same day?

If not I can probably kludge together a VBA routine earlier in the process to run through the list and overwrite duplicates rather than adding them.

--- Also - what is the -- for in the formula? I've not seen that before.

[NBVC]

Sumproduct won't work for that then....

To get first match, try:

Please Login or Register  to view this content.

then you can hide the 0's by formatting the table cells as 0;-0;;@

The -- in the Sumproduct are coerces that convert arrays of TRUE/FALSE to 1/0 respectively for the math to be performed

[flippertie]

Thanks again.

The new formula works - I shall step through it in the formula toolbar in an attempt to understand..

I used Sumproduct (for the first time) to calculate the number of entries column without the -- coerce - and it worked. I'll have to work on that one as well.

So much to learn

[NBVC]

Here is a good article on Sumproduct and it's many uses.