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.
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.
fix the div0 perhaps so it returns 0
but then your average will be out if you dont want to include 0 values anyway
Last edited by martindwilson; 04-23-2009 at 04:35 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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?
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?
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.
select the cell with div0 in whats in the formula bar?
whats the formula that disappears then?
Try this array formula:
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.={AVERAGE(IF(NOT(ISERROR(b1:b10)),b1b10,""))}
Hi subs23,
I think you can attach a sample file for solving your problem faster.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks