average scores a 'name'

Can someone simplify this formula so I would not need to press and hold Ctrl+Shift then press Enter so that the formula works. I use a Excel 2003

Formula is:

Please Login or Register  to view this content.

Range1 = a range name for varying dates
Range2 = a range name for varying names
Score = a range name for Corresponding Scores
A1 = a date
A2 = a name

Purpose of the above formula is to get the average scores a 'name' has acquired at a given 'date.'

hi Daimez,
This formula is already nice & tidy. I think it is quite unlikely that this can be simplified any more & still do what you want. I would say that [ctrl + shift + enter] is a small price to pay considering the functionality you achieve by using the "array formula" (try Googling this or looking in the Excel Help files). Other possible solutions may involve the use of Helper Columns or may even involve the use of a Pivot Table but this will take up a more space compared to the single cell solution that you already have.

If you are concerend about other users (or yourself) stopping the formula from working, I suggest that you Lock the cells using Format - Cells - Protection & tick "locked", then lock the spreadsheet, under Tools - Protect.

Hth
Rob

hth
Rob

A SUM Array is essentially an alternative to Sumproduct (non CSE) so you can switch to a 2 Sumproduct approach:

=SUMPRODUCT(--(Range1=A1),--(Range2=A1),Score)/SUMPRODUCT(--(Range1=A1),--(Range2=A2))

I use the -- operator to coerce the Booleans rather than * as per your example, it's arguably quicker.

You could dispose of the double Sumproduct by using an AVERAGE function, however, this would necessitate CSE Array:

=AVERAGE(IF((Range1=A1)*(Range2=A2),Score))