a formula with non sequential cell references.

Question

It seems like there should be an easy solution to this but I am not finding it so I'd appreciate any help I can get. 

I have a long list of products with prices for each item from 4 different merchants each.  The way it is formatted is that each merchant has two columns.  One column is for price and the other is for the quatntity that the given price represents. (i.e. 2.00 for 1lb of beef from one merchant and 3.00 for 1lb of beef from another.)  I have an old set of data and a new set of data on two separate worksheets and I am trying to see how the new average price for each product has change from the old average price of the same item. 

In some cases not all four merchants were found so there is an empty cell where the price and quantity of the 3rd or 4th merchant would be.  For that reason I was thinking that an AVERAGEIF statement would work because I don't want to include a zero value in the average.  the problem is that putting in the cell range like this G2:G9 doesn't work because that will also include the quantity cells which I don't want to average.  If I hold control and select the cells that I want it puts a comma between them but then the formula thinks there are too many arguments because in the formula "," is what goes between the range and the condition.

Is there some other character or method for using a non-sequential range for an equation like this?

Thanks for the help

Tim

Answer 1

>Also is this statement asking if the sum of that range is >0 or if each cell in that range is >0?

We are getting caught up in words - better to post an example of what is actually in A3:A8, and what result you want from the example.

Answer 2

Ok that helps my understanding a little.  so I think what I am really looking for then is

=averageif(a3:A8,">0",a3,a5,a7)

so that the non-sequential cells are averaged based on the sequential range having value.  but I'm assuming that this will still see the "," between the cells as a new argument and not let me do it.  Is there a different way to format the non-sequential cells portion of this?

Also is this statement asking if the sum of that range is >0 or if each cell in that range is >0?  my hope is that it is the later so that any cell with no value won't be averaged in and mess up my result.

Thanks a ton for the quick response.

Tim

Answer 3

The idea behind AVERAGEIF is that your conditions select the cells to average, rather than you.

=AVERAGEIF(A:A,"Beef",G:G)

will average those cells in G where the cell in the same row but in column A is "Beef"

If you have multiple condtions, then this will average G if G > 0 and A has "Beef"

=AVERAGEIFS(G:G,A:A,"Beef",G:G,">0")

Note that for AVERAGIF, the third range is averaged, but for AVERAGIFS, the first range is averaged.