#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

 

[monere]

https://www.dropbox.com/s/7tdyem4qadzpqbi/dsdasdasd.xls?dl=0

OK, and here's what I need to do...

First of all, there are 8 sheets in the document. There's the main sheet, called TOTAL (which is where I want all the magic to happen), and then there's one sheet for each month of the year starting with Iunie (that's June in my language), to Decembrie (December). You can see them, right?

Now, sheets Decembrie, Noiembrie, Octombrie and Septembrie are almost blank cause I didn't populate them with any data yet, as these months didn't come yet. I will update them as time goes by and obviously that each time I update a sheet I want the main sheet (TOTAL) to include the data of the updated sheet in its formulas and return an updated result into each cell of its rows/columns.

Also, please note that row 1 and column A in each sheet are blank. This is because I removed all writing in those cells so as to hide sensitive info about my company. This will not harm us though as those fields (row 1 and column A) will NOT be included in calculations/formulas/whatever. As I said, they were just writing.

And last but not least, only focus on rows 3 to 51. Yes, I know, there are other randomly spread cells throughout the document that display numbers, but I assure you that those numbers/cells are calculated separately, they are NOT included in the formulas we will need

OK, with these out of the way here's the problem: as you can see there are 8 columns in each sheet. You can't see their labels but they are A to H. The main problem with my document is with column F (that's the 6th column in what you see) that returns #DIV/0! errors in many cells (including the header cell which is supposed to aggregate all other cells in the column and return the average of them).

Now, visually, I could live with these errors but this is a report I need to show to my manager each month and obviously the report should depict accurate figures and stuff, but there is nothing accurate about a #DIV/0! error

In each cell of column F (the 6th one in our case) there is this formula: =B3/E3 (then B4/E4, B5/E5, etc all the way down to B51/E51). This formula obviously divides cell B3 to E3. and so on and so forth, and the reason why some cells in column F contain DIV/0 errors while others don't is simple (and logical): you just can't divide by 0. I know this (it's math common sense) and you already told me, I know. But, what I want to do (and the reason I started this thread) is ask you what formula I should type into the header cell (the very first one, at the top of the column) to calculate the average of the entire column F while ignoring all #DIV/0! errors and only calculate the average of the values it finds. As you can see, some of the cells in column F have real values in them, so I want the header cell to calculate the averages of those values that exist, while totally ignoring the #DIV/0! cells. This is the first thing. The second thing is that I want the formula to work with future updates of my document. As I said in the beginning (and as you can clearly see if you take a look yourselves) sheets Decembrie, Octombrie, Noiembrie, and Septembrie are currently blank but they will get updated eventually. Well, when I update those sheets there will be some cells in column F of the main sheet that previously contained #DIV/0! errors that will now get populated with numbers. Well, I want those new populated cells to also be automatically included in the calculation by the formula in the header cell of column F.

And last but not least I want this document to be compatible with Excel 2003 as this is the version we run at office. Why we don't use newer versions is beyond me, too, don't worry! But there's nothing I can do about it since I don't decide such things

But anyway, I really need this document to be compatible with Excel 97 (the 2003 version), so please keep this in mind when you give me the formula.

Whew! Such a (probably) simple fix, yet such a complicated explanation LOL

Anyways, hopefully you can understand now what I mean. And I hope there is a formula for what I want. Oh, by the way, the formula I entered into the header cell of column F is: =AVERAGE(F4:F52)

 

[skywriter]

Your average formula is fine, it does not need to be changed.
Here are three formulas to consider.

The first formula will leave the cell blank if column E is blank or 0.

This choice will not change the average function. Meaning for example if you have two cells you are averaging and the first cell is 10 and the second cell is blank, the average function would return 10. If the second cell were 0, the average function would return 5.

The second formula I've given you will return 0, this will change the average function.

The third formula I've given you returns #DIV/0!, the only difference here is I created this. It's a string, not really an error, so it doesn't cause the average function to have an error and it doesn't change the result of the average function just like the first formula I gave you that is blank.

I gave you these options so you can decide what you want to use.

If you want blank or a string #DIV/0! and don't want the average function changed by these cells then use formula 1 or 3.

If you want 0 in the cell and want the average function to be changed by having 0's included in your calculation, then use the second formula.

What you see are the actual results of the formula and the actual results of the Average function in the first row. Nothing is typed in.


Excel 2010

	E	F
1	252,969.10	11.74%
3
4	0.00
5	0.00	0.00%
6	0.00	#DIV/0!

<tbody>
</tbody>

TOTAL​

Worksheet Formulas Cell Formula E1 =SUM(E4:E203) F1 =AVERAGE(F4:F52) F4 =IF(OR(E4="",E4=0),"",B4/E4) F5 =IF(OR(E5="",E5=0),0,B5/E5) F6 =IF(OR(E6="",E6=0),"#DIV/0!",B6/E6) <tbody> </tbody>

<tbody>
</tbody>

 

[monere]

You know... this actually works!

I used this formula =IF(OR(E4="",E4=0),"",B4/E4) and it works like a charm. Does EXACTLY what I wanted but didn't know how to explain what I needed

A big, BIG THANK YOU!

Your average formula is fine, it does not need to be changed.
Here are three formulas to consider.

The first formula will leave the cell blank if column E is blank or 0.

This choice will not change the average function. Meaning for example if you have two cells you are averaging and the first cell is 10 and the second cell is blank, the average function would return 10. If the second cell were 0, the average function would return 5.

The second formula I've given you will return 0, this will change the average function.

The third formula I've given you returns #DIV/0!, the only difference here is I created this. It's a string, not really an error, so it doesn't cause the average function to have an error and it doesn't change the result of the average function just like the first formula I gave you that is blank.

I gave you these options so you can decide what you want to use.

If you want blank or a string #DIV/0! and don't want the average function changed by these cells then use formula 1 or 3.

If you want 0 in the cell and want the average function to be changed by having 0's included in your calculation, then use the second formula.

What you see are the actual results of the formula and the actual results of the Average function in the first row. Nothing is typed in.


Excel 2010

	E	F
1	252,969.10	11.74%
3
4	0.00
5	0.00	0.00%
6	0.00	#DIV/0!

<tbody>
</tbody>

TOTAL​

Worksheet Formulas Cell Formula E1 =SUM(E4:E203) F1 =AVERAGE(F4:F52) F4 =IF(OR(E4="",E4=0),"",B4/E4) F5 =IF(OR(E5="",E5=0),0,B5/E5) F6 =IF(OR(E6="",E6=0),"#DIV/0!",B6/E6) <tbody> </tbody>

<tbody>
</tbody>

 

[skywriter]

You know... this actually works!

I knew that.

My pleasure.