# Average calculation with a #Value in the selection

#### JoshuaX1980

##### Board Regular
Hi,
I want to get the average of three cells, one of these cells has no value and is assigned #VALUE (because I calculated with it in other worksheets), now I want the average value of these three cells to be the average value of the two remaining cells. So:

4
#value
6

I want the outcome of Average(A1:A3) to be 5. Now I get #value as an outcome.

I do not want the outcome to become (4+6+0)/3=3.3 ( no zero value for the #value)
I hope somebody can help me.

Cheers,
Lennart

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
JoshuaX1980

Perhaps something like this? Formula in A8 is:
=AVERAGE(IF(ISNUMBER(A1:A7),A1:A7))
confirmed with CTRL+SHIFT+ENTER
Mr Excel.xls
ABCD
14
2#VALUE!
3#VALUE!
47
54
6dasdasD
77
85.5
9
Sheet1

Ok thank you, it is working!

Replies
12
Views
577
Replies
8
Views
711
Replies
1
Views
630
Replies
1
Views
175
Replies
13
Views
638

1,203,483
Messages
6,055,675
Members
444,807
Latest member
RustyExcel

### 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.

### Which adblocker are you using?

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

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