I require an IF Formula (I believe, could be wrong!) where the criteria is if the cell below doesn't equal zero, to count the zero in the above cell, but to ignore all the other zeroes in the row when averaging.

✔ Best Answer

While I would like to take your word for it when you say "I can't eliminate the zeroes", without knowing what formulas create the zeroes, I can't. Nothing personal. :-) Would you care to share a example of the formulas?

In the meantime, what about this?

=SUM(B3:AA3)/COUNTIF(B4:AA4,"<>0")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

I'm confused by the "cells below and the cells above" references. Also by the "in the row' reference. I can't tell where your numbers are, where your formula is, etc.

Please clarify, possibly by posting some example data. Please click on the following line and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yep I knew it would do someones head in, thought I would give it a go explaining this way in the hopes it might make sense! Apologies, I shall try again. (Also thanks for the link!!)

Fortnight 1AB 2AB 3AB 4AB 5AB 6AB Quick Clean 2232 1843 2329 2169 0 0 Standard Clean 3103 3631 2964 3643 0 0 Full Cleans 72 59 29 0 0 0 Totem Poles 1297 1483 1407 1473 0 0In the Full Clean Row, you can see that under 4AB the quantity of Full Cleans completed for that week is zero. There is a formula in the column after 6AB (not in the above table) where I've asked for excel to Average the numbers in all Full Cleans but ignore the zeroes (so the average will work)

The problem with this is that I now need the zero under 4AB to be included as part of the average formula, but the only way I can think of achieving this is to create a formula where the criteria could be that if there is a number above 0 in the Totem Poles row under 4AB, that it would no longer ignore the zero in the above cell (in this case, Full cleans under 4AB).

Please let me know if this is still confusing, I will be happy to try and explain it a bit better if this is the case!

Any help is much appreciated :)

message edited by annettesa

The data you posted is confusing, as Excel uses Column LettersandRow Numbers, so I have no idea what yours mean.As for Averaging data, in Excel the =AVERAGE() function considers Zero as a valid number, so it will be used in it's calculation.

If you want to

Exclude Zerosin your average calculation, then using the =AVERAGEIF() function will work.With your data like:

A B C D E F G 1) Quick Clean 2232 1843 2329 2169 0 0 2) Standard Clean 3103 3631 2964 3643 0 0 3) Full Cleans 72 59 29 0 0 0 4) Totem Poles 1297 1483 1407 1473 0 0To average Full Cleans,

excluding zeros, something like:=AVERAGEIF(B3:G3,">0")

should work.

Is that what your looking for?

MIKE

I'm still confused. You said: "I now need the zero under 4AB to be included as part of the average formula"

The AVERAGE function in Excel includes zeros in its calculations.

Why not just use that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Please refer to the new grid below including the Cell References from Excel (hopefully this will help me explain it better!) I've already created a formula at the end of Row 3 that ignores the zeroes to complete the average. This isn't in the spreadsheet below but the formula I've used is:

=AVERAGEIF(B6:AA6,"<>0")I'm trying to find out if there is a way to complete an average of all Row 3 where all of the zeroes are ignored (which I've already done), I'm able to create an exception based on the fact that the E4 cell below the zero I want included in the average (E3) has a number greater than zero.

If you think of the columns as weeks, I want an average at the end of the rows including all numbers for that week (eg. all quantities in Column E inclusive of any zeroes to show a true average at the end of each row for that type of clean) but I don't want future weeks zero numbers to be included (which is the ignore formula I've already created).

A B C D E F G 1 Quick Clean 2232 1843 2329 2169 0 0 2 Standard 3103 3631 2964 3643 0 0 3 Full Cleans 72 59 29 0 0 0 4 Totem Poles 1297 1483 1407 1473 0 0It might be impossible to explain it further, but if it still doesn't make sense please feel free to ask me more questions and I will be happy to work with you guys to figure this one out. Thanks for the feedback!

message edited by annettesa

I think I get the gist of what you are trying to do, but something is still confusing. re: "I've already created a formula at the end of

Row 3that ignores the zeroes to complete the average."re: "the formula I've used is:=AVERAGEIF

(B6:AA6,"<>0")Why do you mention a formula for Row 3, but actually reference Row 6?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Please ignore the 6, I just grabbed the formula as an example and grabbed the wrong row. In row 3 it will be =AVERAGEIF(B3:AA3,"<>0") I will just be using this formula in every row.

Sorry for the confusion!

A quick try... Eliminate the zeros, leaving any cell that now contains a zero "blank", then use this:

=SUM(B3:AA3)/COUNT(B4:AA4)

The COUNT function will only cells that contain numbers. A blank cell will not be counted, but a zero would be.

In your example, that formula would be equivalent to:

=SUM(B3:AA3)/4

If that doesn't work, tell me why. That may give me a deeper understanding of what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I can't eliminate the zeroes or manipulate any of this data as they are all set up as formula's to pick up data from another tab. My goal is to not have to update the average formula each week to only grab the range of numbers I need. I'm not certain if this is achievable yet, but I'm hopeful!

While I would like to take your word for it when you say "I can't eliminate the zeroes", without knowing what formulas create the zeroes, I can't. Nothing personal. :-) Would you care to share a example of the formulas?

In the meantime, what about this?

=SUM(B3:AA3)/COUNTIF(B4:AA4,"<>0")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

IT WORKS!!!! THANK YOU INTERNET STRANGER!!!!!!!!!!!!! I didn't take it personally either lol :) It's pretty much a Summary Sheet and in every cell where you can see numbers, the formula's are

='4AB'!M9

4AB is the name of the tab and M9 is the cell on this tab that it's pulling the data from, so it changes with each week/each row.

But anyhoo, I've chucked the formula you've provided at the end of the row and it has worked perfectly, can't thank you enough!

I'm glad it worked for you. You might want to try this just to see if it works. At this point it would just be a learning experience for you.

Change your formula to:

=IF('4AB'!M9=0,"",'4AB'!M9)

If that eliminates the zeroes, then the formula I posted in Response #8 might work also.

Basically what that does is make the cell "blank" if there is no value to pull from the other sheet and then the COUNT function should work.

In other words, it "eliminates" the zeroes.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History