div/0 error problem

[subs23]

Hello,

I am new to this site. Need help with an Average b1:b10. The formula wont work because b5 has a div/o error. How do I make the average formula ignore this error.

[martindwilson]

fix the div0 perhaps so it returns 0
but then your average will be out if you dont want to include 0 values anyway

[subs23]

I am getting the div/o error when I copy and paste...the formulas disapear and this causes a div/o error. why would the formulas disapear?

[martindwilson]

select the cell with div0 in whats in the formula bar?

[subs23]

I am getting the div/o error when I copy and paste...the formulas disapear and this causes a div/o error. why would the formulas disapear?

[subs23]

Its pulling the info from a different spreadsheet. And when I copy and paste that spreadsheet the formula disapears and leaves a blank which is causing the Div/o error.

[martindwilson]

whats the formula that disappears then?

[Justin Young]

Try this array formula:

Please Login or Register  to view this content.

Don't type the curly braces - they are there to remind you that this is an array formula which means it returns results from many cells. When you put the formula in dont press Enter. Instead press Ctrl+Shift+Enter. You will see that Excel puts the curly braces around the formula when you do that.

[windknife]

Hi subs23,

I think you can attach a sample file for solving your problem faster.