Multi-cell array

  • Thread starter Thread starter nobody
  • Start date Start date
N

nobody

Ok, what am I doing wrong. I am trying to use an array function to find out
what the average % D is of C for a name in cell e1 between 7/7 and 7/9

A B C D E
date name totala totalb JOE
7/7 JOE 100 20 7/7 (START DATE)
7/7 SAM 100 15 7/9 (END DATE)
7/7 BARB 100 35
7/8 JOE 90 9
7/8 SAM 100 17
7/9 JOE 100 23

{=MAX(IF((E1=B2:B7)*(E2>=A2:A7)*(E3<=A2:A7),(SUM(D2:D7)/SUM(C2:C7)))))}

I am not getting any errors, just no data.

Any ideas?

Thanks
 
Nobody
The formula you have, as written, is not a formula. It is text because
the first character is {. I didn't get into your formula, but if it is an
array formula, write it without the {} and do Ctrl-Shift-Enter instead of
just Enter. Excel will add the {}. HTH Otto
 
I understood that the{ } were added by XL - I was just expressing that it
was an array formula, with the appropriate ctrl-shift-enter - I guess I
didn't amke that clear -

Anyway, I got it to work with the following array formula ({ } omitted for
clarity)

=SUMPRODUCT(IF((E3=B2:B7)*(E3<=A2:A7)*(E3>=A2:A7),(D2:D7)/SUMPRODUCT(IF((E3=
B2:B7)*(E3<=A2:A7)*(E3>=A2:A7),(C2:C7)))

Seems like I had the <> turned around - always the simple mistakes that bite
you the worst. Thank you for your suggestion though.
 
Back
Top