Need an average of a range.

[Ryan1996]

I need to create an average of the range below into the top box however due to the cells being both numbers and text it doesn't seem to let me use =AVERAGE()

Can anyone help me get the average of my range to show into the top box. See below for range and cells.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD](Formula to go here)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD]1 - Very Inadequate / Major Non-Conformity[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD]2 - Inadequate / Minor Non-Conformity (high severity)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD]3 - Partially adequate / Minor Non-Conformity (low severity)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD]3 - Partially adequate / Minor Non-Conformity (low severity)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD]3 - Partially adequate / Minor Non-Conformity (low severity)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Obviously the average is 3 here however i need to a formula to automatically do this for the whole column based on specific cells. If someone could help me with the first one i can set it up for the rest.

 

[sandy666]

at first look, maybe

=AVERAGE(IFERROR(--MID(H$2:H$6,1,IFERROR(SEARCH(" ",H$2:H$6),0)-1),0))

CSE entered

 

[steve the fish]

Sounds like you want the mode. Maybe:

=INDEX(H2:H100,MODE(IF(H2:H100<>"",MATCH(H2:H100,H2:H100,0)*{1,1})))

Enter CTRL-SHIFT-ENTER

 

[drallab57]

Ryan

This looks as though you may be involved with an ISO system such as 9001. If so, this is what I do too.

An average is straight forward . . . and I suggest that the average here would be (12/5=) 2.4, or is it the median you are after? I assume the former.

Are the cells H2:H60 the result of a concatenation (joining together) of a number of other cells? I imagine they are in that the initial value (1,2 or 3) appears to determine the rest of the contents of the cell. If so, just AVERAGE that column placing the result in H1. This will be the average value of all the cells that have a value.

If you want to come back for further ideas let me know.

 

[Ryan1996]

Thanks to all that replied.

So indeed, i am working with ISO however it's with 27001. Essentially i have a spreadsheet with each control. Within each of those controls there is the overall score for that control and then the sub controls. I want to be able to find out the average (1/2/3/4/5, might end up being 2.4 or 3.5 etc) of the sub controls into the overall score. So my overall would be H2 and then my range (The sub controls) would be H3:H10.

Maybe i need to just use numbers and then add comments for the naming convention of these numbers? If possible i would like to do it this way though

Ryan

This looks as though you may be involved with an ISO system such as 9001. If so, this is what I do too.

An average is straight forward . . . and I suggest that the average here would be (12/5=) 2.4, or is it the median you are after? I assume the former.

Are the cells H2:H60 the result of a concatenation (joining together) of a number of other cells? I imagine they are in that the initial value (1,2 or 3) appears to determine the rest of the contents of the cell. If so, just AVERAGE that column placing the result in H1. This will be the average value of all the cells that have a value.

If you want to come back for further ideas let me know.

 

[steve the fish]

You were given a way to do that by sandy. Id make a slight adjustment though in case of blank cells:

=AVERAGE(IFERROR(--MID(H2:H100,1,SEARCH(" ",H2:H100&" ")-1),""))

 

[drallab57]

Sandy - neat way to do it but it does not allow for blank cells, they are in the average as having the value 0.

 

[Ryan1996]

I have tried that and none of the cells are blank however it still returns 0, as for your version it returns #VALUE ! even though it should work.

 

[steve the fish]

Did you follow this:

Enter CTRL-SHIFT-ENTER

 

[Ryan1996]

I did, i've made a change and it's seemed to worked. Sorry for the long process here. Thanks for the help!