Average calculation with a #Value in the selection

JoshuaX1980

Board Regular
Joined
Jan 26, 2005
Messages
80
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

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?

Disable AdBlock

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
Back
Top