average ifs

[Pottie8]

Hi all

I have a data sheet and a report sheet.

(Data) I need the average (displayed in report column B) of the percentages in data column C, if the value in data column B matches the set values in report column A of results. Example:

DATA:
[TABLE="width: 306"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Date[/TD]
[TD]Pet[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]2017-06-04[/TD]
[TD]SNAKE[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]2017-06-15[/TD]
[TD]DOG[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]2017-06-20[/TD]
[TD]LADYBUG[/TD]
[TD="align: right"]60%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]2017-06-20[/TD]
[TD]DOG[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]2017-06-20[/TD]
[TD]SNAKE[/TD]
[TD="align: right"]70%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]2017-07-07[/TD]
[TD]CAT[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]2017-07-07[/TD]
[TD]BIRD[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]2017-07-07[/TD]
[TD]RAT[/TD]
[TD="align: right"]40%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]2017-07-10[/TD]
[TD]DOG[/TD]
[TD="align: right"]60%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]2017-07-10[/TD]
[TD]LADYBUG[/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]2017-07-10[/TD]
[TD]DOG[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]2017-07-12[/TD]
[TD]FISH[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]2017-08-03[/TD]
[TD]SNAKE[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]2017-06-15[/TD]
[TD]DOG[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]2017-07-11[/TD]
[TD]LADYBUG[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]2017-07-15[/TD]
[TD]DOG[/TD]
[TD="align: right"]70%[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]2017-08-03[/TD]
[TD]SNAKE[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]2017-07-11[/TD]
[TD]CAT[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]2017-07-12[/TD]
[TD]BIRD[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]2017-07-10[/TD]
[TD]SNAKE[/TD]
[TD="align: right"]70%[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]2017-06-03[/TD]
[TD]DOG[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]2017-06-04[/TD]
[TD]LADYBUG[/TD]
[TD="align: right"]20%[/TD]
[/TR]
</tbody>[/TABLE]



REPORT:

[TABLE="width: 420"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]TEXT[/TD]
[TD]AVERAGE[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]DOG[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CAT[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]FISH[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]BIRD[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]SNAKE[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]RAT[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]LADYBUG[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Try

=AVERAGEIF(Data!B$2:B$23,A2,Data!C$2:C$23)


However, consider that this may not be accurate depending on how those percentages were achieved in the first place.
The Average of Percentages will not be truely accurate if the divisor of each percentage is different.

i.e. Take DOG
If Dog on Row 3 is 80%, resulting from say 8/10
If Dog on Row 10 is 60%, resulting from Say 12/20

Then average(8/10, 12/20) is NOT the same result as 20/30

 

[Pottie8]

Hi There

Thank you**

I have noted however that data being entered into DATA afterwards at the bottom does not get included in the calculations.

I have updated the formula's to included the range of added records.

Any advise?

New data will be added in rows at the bottom constantly.


Thanks!!

 

[Aladin Akyurek]

One option...

=AVERAGEIFS(Data!C:C,Data!B:B,A2)

if you intend to include new records automatically.

 

[Pottie8]

Thanks so much.

Formula pulls through data, but it is weird in that newly added data lines doesnt seem to get calculated.

only once manually retyped. I have formatted as relevant number, still issues. any furhter ideas?


Regards,
pottie8

 

[Aladin Akyurek]

Thanks so much.

Formula pulls through data, but it is weird in that newly added data lines doesnt seem to get calculated.

only once manually retyped. I have formatted as relevant number, still issues. any furhter ideas?

Regards,
pottie8

Perhaps the new additions are text numbers, not true numbers...

Control+shift+enter, not just enter:

=AVERAGE(IF(Data!B:B=A2,IF(Data!C:C+0,Data!C:C)))

Are the results different?