Help & FAQs    Search BCF    Members    Groups    Events Calendar
Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


Excel spreadsheet #DIV/0!

 Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

steven_191
Nearly there...



Joined: 31 May 2009
Karma :
PostPosted: 13:54 - 13 Apr 2011    Post subject: Excel spreadsheet #DIV/0! Reply with quote
My cell has

=AVERAGE(D12:D36)

But there's nothing in those cells yet so I'm getting the error. How do I make the error go away without putting a 0 in one of the cells.

I also get the error in cells that are using other calculations based on the cells that already have the error. And also in cells that have 0s in but no real number to calculate eg

=(F12/100)*(F8/(D8/100))

F12=0
F8=0
D8=0

These cells are awaiting other inputs before they calculate a number.
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

The Artist
Super Spammer



Joined: 06 Jan 2008
Karma :
PostPosted: 13:55 - 13 Apr 2011    Post subject: Reply with quote
You tried formatting the cells as something else? Custom perhaps.
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

0ddball
World Chat Champion



Joined: 15 Jul 2005
Karma :
PostPosted: 15:06 - 13 Apr 2011    Post subject: Reply with quote
Try =AVERAGE(IF(NOT(ISERROR(D12:D36)),D12:D36))

Make sure to press CTRL-SHIFT-ENTER after entering it in the formula bar, not just enter.
____________________
ZXR750L
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

steven_191
Nearly there...



Joined: 31 May 2009
Karma :
PostPosted: 15:35 - 13 Apr 2011    Post subject: Reply with quote
0ddball wrote:
Try =AVERAGE(IF(NOT(ISERROR(D12:D36)),D12:D36))

Make sure to press CTRL-SHIFT-ENTER after entering it in the formula bar, not just enter.



It got rid of the error but the calculation is wrong now.
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

MarkJ
World Chat Champion



Joined: 11 Apr 2006
Karma :
PostPosted: 16:10 - 13 Apr 2011    Post subject: Reply with quote
I got this from the help file:
Quote:
Prevent the error value from being displayed by using the IF worksheet function. You can then display 0 or any string as the result.
For example, if the formula that produces the error is =A1/A2, use =IF(A2=0,"",A1/A2) to return an empty string, or =IF(A2=0,0,A1/A2) to return 0.

So...
=IF(D12=0,0,AVERAGE(D12:D36))
 Back to top
View user's profile Send private message You must be logged in to rate posts

Pete.
Super Spammer



Joined: 22 Aug 2006
Karma :
PostPosted: 17:43 - 13 Apr 2011    Post subject: Reply with quote
Try this

=averageif(D12:D36; "<>0")
____________________
a.k.a 'Geri'

132.9mph off and walked away. Gear is good, gear is good, gear is very very good Very Happy
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

steven_191
Nearly there...



Joined: 31 May 2009
Karma :
PostPosted: 16:29 - 14 Apr 2011    Post subject: Reply with quote
MarkJ wrote:
I got this from the help file:
Quote:
Prevent the error value from being displayed by using the IF worksheet function. You can then display 0 or any string as the result.
For example, if the formula that produces the error is =A1/A2, use =IF(A2=0,"",A1/A2) to return an empty string, or =IF(A2=0,0,A1/A2) to return 0.

So...
=IF(D12=0,0,AVERAGE(D12:D36))


This worked. Just need to try to remove the error now from a calculation which takes a number fro
Each of twelve sheets. This equation only seems to work for the sums on the same sheet. Unless I'm doing it wrong.

Something like

=AVERAGE(january!G10,feb!G10.... Etc )

But they aren't filled out yet and so get an error. Forthe previous calculation I have nominated a cell that is related to the final outcome of the sum. Eg if the sum require a number from g1 and h1 but h1 get and number from i1 and k1 I've picked the appropriate cells so that when you fill out the box the error doesn't have a chance to become apparent. I wouldn't want to wait twelve months for a final figure for this one. I want it to take an average of the numbers that are there not including blank cells obviously. Can I do some sort of only consider if it's >1 ??
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

400bandit
World Chat Champion



Joined: 21 Dec 2005
Karma :
PostPosted: 22:47 - 18 Apr 2011    Post subject: Reply with quote
=IF(ISERROR(AVERAGE(D12:D36)),0,(AVERAGE(D12:D36))

Simples.

Joe
____________________
Current Bikes 650 Honda Bros, VTR1000 Firestorm // Past Bikes: Sachs Madass 50/110cc - Bandit 400 - FZR750
(+ a XS650 in lots of pieces in my garage and other places...)
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

400bandit
World Chat Champion



Joined: 21 Dec 2005
Karma :
PostPosted: 22:52 - 18 Apr 2011    Post subject: Reply with quote
Edit: I only read your first post. What I posted may still work though I am not entirely sure what you need. If it isn't sensitive data feel free to pm me and I'll send you my email address to which you could send the project or even a stripped down version of it with fake data.

Kind Regards,

Joe
____________________
Current Bikes 650 Honda Bros, VTR1000 Firestorm // Past Bikes: Sachs Madass 50/110cc - Bandit 400 - FZR750
(+ a XS650 in lots of pieces in my garage and other places...)
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts
Old Thread Alert!

The last post was made 15 years, 193 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 
Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.06 Sec - Server Load: 0.42 - MySQL Queries: 13 - Page Size: 61.18 Kb