Index Match to return an average

[Batley]

I need a formula to look at a table of data, identify if the result is TRUE then return me the average of all the true values for each row:

		1	2	3	4	5	6	7	8	9	10	11
		3547	3371	3034	2982	3156	3207	2849	3120	3388.869	3616.027	3866.535
Average for TRUE	Number	10	5	2	5	9	6	3	24	9.5	24.5	17.25
0	1	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4	2	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
10	3	FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE
7.25	4	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
	5	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE
	6	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE

I have manually entered the first few results I want to get.
So there are no True results for number 1 so the average is 0
For number 2 there are true results for col F which and col I so I want it to add the blue numbers in row 3 together (5+3) then get the average of 8
How would I do this with a formula?
I used and AND formula to return the TRUE/FALSE result I just cant figure out the next step.
Any help would be greatly appreciated

 

[Joe4]

If your numbers are in row 3, and number 2 is on row 5, use this formula to get your average of 4:

=SUMIF(C5:M5,TRUE,C$3:M$3)/COUNTIF(C5:M5,TRUE)

 

[Joe4]

A minor update to the formula to handle number 1, where there are no matches.
This version will return 0 instead of an error (put formula in cell A4):

=SUMIF(C4:M4,TRUE,C$3:M$3)/MAX(COUNTIF(C4:M4,TRUE),1)

This version can be used for all rows.

 

[Batley]

=ROUNDUP(IFERROR(SUMIF(C4:BB4,TRUE,C$3:BB$3)/COUNTIF(C4:BB4,TRUE),"0"),0)
Thank you for your help, above is the formula I ended up with. I fixed the error by adding in IFERROR and also added a round up function - it seems to work

 

[Joe4]

OK, a few things to note:
- your formula will round up to the nearest whole number, do you will lost your decimal values (i.e 7.25 will show as 8)
- in your IFERROR formula, you should really write "0" as 0 (without the double-quotes). Double-quotes are used for Text, not Numbers. The ROUND function coerces it back to a number, but it is important something to note in the future, as return TEXT entries could affect calculations you may be trying to do
- also note that IFERROR will handle ANY error, not just 0 counts for division. So if you had a text error entry where it expects a number, that situation will be hidden by the IFERROR statement. Sometimes, you may want to see/know about that situation, in which case it would be preferable to use the formula I provided. It all depends on your needs, but you should just be aware of that.

 

[Batley]

Thank you so much for that information - I had no idea "" changed a number to text and its a mistake I have been making for a while!
Self taught excel I am afraid but I am learning more everyday and really appreciate you have taken the time to help me - I will amend the formula

 

[Joe4]

Thank you so much for that information - I had no idea "" changed a number to text and its a mistake I have been making for a while!
Self taught excel I am afraid but I am learning more everyday and really appreciate you have taken the time to help me - I will amend the formula

You are welcome.

In Excel and VBA, double-quotes are used to denote literal text values.
Many times, it may not ultimately make a difference (wrapping a number in "") as sometimes Excel might automatically coerce back to a number based on the formulas you are putting it in, but sometimes it might not, so it is best to be consistent to avoid any potential problems.