#DIV/0 error!

[monere]

hi,

I don't know how to explain, but I'll try

I have 5 worksheets (each of them being 8 columns x 52 rows... if this matters) with the first worksheet aggregating (totaling, averaging, etc.) data from the other 4.

Now, the secondary worksheets are fine and I can fiddle with them as I wish without running into any errors (probably because they don't need to aggregate data from other worksheets but only add,divide, etc. cells in their own selves).

But... the main worksheet is a bit tricky since it has to pull data from the other 4 sheets. Namely, I put this formula: =AVERAGE(F4:F52) (where f4:f52 are the rows I mentioned earlier) in one of the columns, in order to - obviously - get the average of all the 52 rows, where each row will calculate and pull the average from the other 4 worksheets. As I said, the formula above works like it should for the other worksheets, but for the main sheet it returns a #DIV/0 error. And the reason it returns this error is because some cells where the data is being pulled from have zeros. Which again is normal (since those cells really need to have zeroes). The problem is not that there are zeroes in those cells, but that I don't know the formula to automatically calculate averages just for the cells that have positive values (ignoring the cells with zeros in them).

If I could find out the formula to do this I would probably get rid of the #DIV/0 error too, but... I don't know that formula

Which is why I am asking here. Hopefully one of you handsome guys and gals (you ARE handsome, right?) understood my issue and wants to help me out. That would be so GREAT!

TIA and awaiting for your rescue

 

[skywriter]

I did a little experiment with the average function. If I have a range that has zeros in it I don't get an error, if I have a range that has all zeros I don't get an error. The only time I get an error is when there are no numbers at all in the range. If I have 10 blank cells in a 10 cell range I get an error, if I add just one zero the error goes away.

Is you data F4:F52 on the same sheet that you formula is? If it's on a different sheet I would expect to see the sheet name in front of the F4:F52. If it is on a different sheet I would just suggest selecting the range and not typing it into your formula that way you know the syntax is correct.

 

[ghrain22]

even if you have 0's in your data, that' shouldn't return a div/0 error. If you're getting a div/0 error, it means it's not finding ANY numbers in your range. Is it possible your numbers are being formatted as text?

If that's the case you could try using the value formula:
=Average(Value(F4:F52))
Entered as an Array formula. Ctrl+shft+enter (not just enter)

 

[monere]

Ugh, sorry folks. I made some confusions... my bad

I actually have 2 issues that I need to fix, so I'll re-explain...

1) Issue 1:

Sheets 2, 3 and 4 need a simple average calculation formula, which I know how to do: =average(f4:f52). The problem with this is that indeed there are some rows that have blank cells, so when I put that formula into the header (above the header actually, cause that's where I sum/average everything) i get that #DIV/0! error. So, how do I calculate the averages when I have blank cells? That's the first issue

2) Issue 2:

now, assuming issue 1 is solved, I then want sheet 1 to automatically make an average from the values of row1 of each secondary sheet (2,3 and 4) and automatically insert that average value into row1 of itself (the main sheet that is). And I want this to automatically happen for each of the 52 rows of the main sheet, so I'll probably have to enter the correct formula into row1 and then drag the handle all the way down until all 52 rows autopopulate with the formula. But, the question is: which formula are we talking about cause I am clueless?

This is issue #2.

Again, sorry for not explaining correctly but I'm a bit tired of working with formulas all day long at work and my head is spinning and I only see functions and numbers when I close my eyes lol

Anyways, please let me know. I even say "pretty please"

 

[joeu2004]

I put this formula: =AVERAGE(F4:F52) (where f4:f52 are the rows I mentioned earlier) in one of the columns, in order to - obviously - get the average of all the 52 rows, where each row will calculate and pull the average from the other 4 worksheets. As I said, the formula above works like it should for the other worksheets, but for the main sheet it returns a #DIV/0 error. And the reason it returns this error is because some cells where the data is being pulled from have zeros.

No. AVERAGE(F4:F52) returns #DIV/0 only if there are no numeric values in the range.

Note that I write "numeric values", not "numbers". If you think you do have numbers in that range, including zeros, perhaps they are text, not truly numeric values.

Use =ISTEXT(F4) to confirm; apply the formula to each of F4:F52.

It would behoove you to ensure that any numeric text is converted to truly numeric values first. One method: use the Text To Column feature.

Which again is normal (since those cells really need to have zeroes). The problem is not that there are zeroes in those cells, but that I don't know the formula to automatically calculate averages just for the cells that have positive values (ignoring the cells with zeros in them).

If you are using Excel 2007 or later and you do not require Excel 2003 compatibility (i.e. not saving as ".xls"), use:

=AVERAGEIF(F4:F52,">0")

If you require Excel 2003 compatibility, array-enter the following formula (press ctl+shift+Enter instead of just Enter):

=AVERAGE(IF(F4:F52>0,F4:F52))

If F4:F52 contains numeric text and you do not want to convert it to truly numeric values (not recommended), array-enter the following formula:

=AVERAGE(IF(--F4:F52>0,--F4:F52))

The double-negate (--) converts numeric text to truly numeric values. Any equivalent arithmetic would do the same thing; for example, multiply by one or add zero.

 

[ghrain22]

1) Issue 1:

Sheets 2, 3 and 4 need a simple average calculation formula, which I know how to do: =average(f4:f52). The problem with this is that indeed there are some rows that have blank cells, so when I put that formula into the header (above the header actually, cause that's where I sum/average everything) i get that #DIV/0! error. So, how do I calculate the averages when I have blank cells? That's the first issue

Mine and skywriters point is that a few blanks cells won't cause a div/0 error. Only instances where you don't have ANY numbers in your reference. So either your reference isn't right/not capturing the correct cells (sky's solution), or your numbers are actually coming through as text instead of numbers (my solution)

now, assuming issue 1 is solved, I then want sheet 1 to automatically make an average from the values of row1 of each secondary sheet (2,3 and 4) and automatically insert that average value into row1 of itself (the main sheet that is). And I want this to automatically happen for each of the 52 rows of the main sheet, so I'll probably have to enter the correct formula into row1 and then drag the handle all the way down until all 52 rows autopopulate with the formula. But, the question is: which formula are we talking about cause I am clueless?

I believe you're looking for a 3 dimensional reference
=AVERAGE(Sheet1:Sheet2!A1:A8)

 

[joeu2004]

1) Issue 1:
Sheets 2, 3 and 4 need a simple average calculation formula, which I know how to do: =average(f4:f52). The problem with this is that indeed there are some rows that have blank cells, so when I put that formula into the header (above the header actually, cause that's where I sum/average everything) i get that #DIV/0! error. So, how do I calculate the averages when I have blank cells?

Our posting crossed on the wire. To reiterate: AVERAGE(F4:F52) returns #DIV/0 only when there are no numeric values in the range. For example, where all of F4:F52 are empty or appear blank.

If that might be the case, one of the following might suffice:

=IFERROR(AVERAGE(F4:F52),0)
or
=IF(COUNT(F4:F52),AVERAGE(F4:F52),0)

Use the latter if you require Excel 2003 compatibility.

 

[Simon4s]

are you dividing a number with a blank cell with the DIV/0 formatted as percentage?

1 divide 0 = DIV/0 error.

I barely skimmed through everyones comments. So i don't really know ur problem. Plug in numbers into all the fields that are referenced and see if u still get the problem

 

[monere]

I have no darn idea what's wrong, I swear. Can I upload/attach the file (I'll remove all sensitive info from it since this is an important file for the company I work for) and send it to you so you can see better what I mean?

I really don't know how to explain better, and I have a feeling that I omitted important stuff from my explanations.

Can I just send/upload/attach/whatever the file to any of you? If yes, how? Maybe upload it to some free sharing sites, or...?

 

[skywriter]

I have no darn idea what's wrong, I swear. Can I upload/attach the file (I'll remove all sensitive info from it since this is an important file for the company I work for) and send it to you so you can see better what I mean?

I really don't know how to explain better, and I have a feeling that I omitted important stuff from my explanations.

Can I just send/upload/attach/whatever the file to any of you? If yes, how? Maybe upload it to some free sharing sites, or...?

https://www.dropbox.com/