# Need an average of a range.

#### Ryan1996

##### Board Regular
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.
H
1(Formula to go here)
2
 1 - Very Inadequate / Major Non-Conformity

<tbody>
</tbody>
3
 2 - Inadequate / Minor Non-Conformity (high severity)

<tbody>
</tbody>
4
 3 - Partially adequate / Minor Non-Conformity (low severity)

<tbody>
</tbody>
5
 3 - Partially adequate / Minor Non-Conformity (low severity)

<tbody>
</tbody>
6
 3 - Partially adequate / Minor Non-Conformity (low severity)

<tbody>
</tbody>

<tbody>
</tbody>
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.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
at first look, maybe
Code:
``=AVERAGE(IFERROR(--MID(H\$2:H\$6,1,IFERROR(SEARCH(" ",H\$2:H\$6),0)-1),0))``
CSE entered

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

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.

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.

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),""))

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

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.

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

Replies
3
Views
832
Replies
1
Views
173
Replies
5
Views
195
Replies
3
Views
120
Replies
1
Views
179

1,203,099
Messages
6,053,523
Members
444,669
Latest member
Renarian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back