Double Excel Formula Problem!!

  • Thread starter Thread starter twogoodtwo
  • Start date Start date
T

twogoodtwo

First ever post so please be gentle and thanks in advance to anyone wh
can help me - I have spent hours trying to get it to work before askin
this question:

I am trying to write 2 formula's:

1) I have 4 columns with data in each columns;

Date, Name, True/False, Value

I would like to write a formula that looks up a specific Name on
specific date and returns the Value. (Bearing in mind that there ar
many dates that are the same but with different Names beside each)

2) Secondly, using the same 4 colums, I would like to work out th
average Value for "TRUE" data points on each particular date. (then d
the same for "FALSE" data points)

If anybody can help it would be most appreciated as I don't know wher
to begin. Or if you need more info, please let me know.

Thanks:confused: :confused
 
Hi

okay assuming that your data range starts at A1 (so headings are A1:D1) and
your data extends to row 100 the following two formulas should work for you:

1) =INDEX(D1:D100,MATCH(1,(A1:A100=G3)*(B1:B100=F3),0))
where the date that you want to look up is in G3
and the name is in F3
this is an array formula so needs to be entered using ctrl & shift & enter
not just enter

2)
=SUMPRODUCT(($A$2:$A$100=F6)*($C$2:$C$100=$G$5)*($D$2:$D$100))/SUMPRODUCT(($
A$2:$A$100=F6)*($C$2:$C$46=$G$5))
where the date that you want to find the average for is in F6, the state
that you want (ie True or False) is in G5
this is not an array formula

Hope this helps
Cheers
JulieD
 
Thanks JulieD for your help - The first formula worked fine but th
second returned "#value!" Any suggestions?

What if I just wanted to sum the relevant data rather than average it
 
sorry, my mistake forgot to change the range it should have read:
=SUMPRODUCT(($A$2:$A$100=F6)*($C$2:$C$100=$G$5)*($D$2:$D$100))/SUMPRODUCT(($
A$2:$A$100=F6)*($C$2:$C$100=$G$5))

the ranges in the sumproduct function must be of equal size

if you want to sum rather than average just use
=SUMPRODUCT(($A$2:$A$100=F6)*($C$2:$C$100=$G$5)*($D$2:$D$100))

Hope this helps
Cheers
JulieD
 
Perfect - Thanks so much for your help.... now for my last question (
will post a new note as it is on a seprate topic
 
Back
Top