Solved IF Formula with weird criteria to create an Average

February 25, 2016 at 14:03:15
Specs: Windows 7
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.

See More: IF Formula with weird criteria to create an Average

Report •

✔ Best Answer
March 1, 2016 at 19:48:23
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



#1
February 25, 2016 at 14:51:15
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.


Report •

#2
February 28, 2016 at 17:17:54
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	0

In 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


Report •

#3
February 28, 2016 at 18:13:49
The data you posted is confusing, as Excel uses Column Letters and Row 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 Zeros in 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    0

To average Full Cleans, excluding zeros, something like:

=AVERAGEIF(B3:G3,">0")

should work.

Is that what your looking for?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 28, 2016 at 19:13:18
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.


Report •

#5
March 1, 2016 at 13:23:44
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	0

It 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


Report •

#6
March 1, 2016 at 13:57:24
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 3 that 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.


Report •

#7
March 1, 2016 at 16:30:37
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!


Report •

#8
March 1, 2016 at 16:53:16
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.


Report •

#9
March 1, 2016 at 17:48:31
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!


Report •

#10
March 1, 2016 at 19:48:23
✔ 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


Report •

#11
March 1, 2016 at 20:22:30
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!


Report •

#12
March 2, 2016 at 03:58:13
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


Report •

Ask Question