Guys i was given a formula in B15 which allows me to get the average of the figures that are submitted within that box not all 28 squares will be used hence the formula but this leaves me a #DIV/0! if no numbers are inserted.i need to get the average of all 7 boxes for totals but cant why i have this error there how can i oberride this so if all the boxes are blank it reads nothing
Last edited by dodger999; 01-07-2010 at 10:14 PM.
Is there a chance you could upload the Excel file without zipping it? My computer, for whatever reason, can't open files from here that are zipped first.
Your answer is probably either =If(Iserror(<current formula>,"",<current formula>) or, if you have 2007, =IFERROR(<current formula>,"").
hope this attachment works for you ..in answer to the other reply how do you mean current formula do i still start with Average(IF then iserror not quite sure thanks for the reply anyway im still a bit of a novice
Ok, that way works, but you should replace "" with 0. You can set your format to 0.00%;-0.00%; and that will show a blank for exactly 0. I don't really know why it returns Div/0 with a blank.
ok replaced with a 0 but it then averages all 28 squares which i dont need i only need to average the squares with a value in so if i put 98.52 in the total goes to 3. something not good
excel 2007 has averageif
=AVERAGEIF(B8:E14,"<>0")
so
=IFERROR(AVERAGEIF(G8:J14,"<>0"),"")
or if pre 2007
=IF(ISERROR(AVERAGE(IF(G8:J14<>0,G8:J14,""))),"",AVERAGE(IF(G8:J14<>0,G8:J14,"")))
array entered
Last edited by martindwilson; 01-07-2010 at 06:45 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
OK, try =IF(ISERROR(Average(B8:E14)),0,Average(B8:E14)) or if you have 2007, =IFERROR(Average(B8:E14),0)
Martin, that still gives a Div/0 error if there is nothing to average.
not when i do it!
Ummm....not seeing where you applied your formula. See attached, cell G15.
guys that was brill thanks for all the help darkyam you got it thanks mate i will put splved in a sec
yellow cell
When I open your file, I see =IF(ISERROR(AVERAGE(IF(G8:J14<>0,G8:J14,""))),"",AVERAGE(IF(G8:J14<>0,G8:J14,""))) in the yellow cell, confirmed with CSE.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks