I need to average this range assuming all cells have a value 0. What's the
correct function to
use???(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+ CZ13+DJ13+DT13+ED13+EN13+EX13+FH13+FR13+GB13+GL13+ GV13+HF13+HP13+HZ13+IJ13)

See an answer to your previous post.

Please don't post the same problem multiple times. It tends to fragment
any answers you get, and potentially wastes the time of those answering
questions that have already been answered. For tips on using these
groups effectively, see

http://cpearson.com/excel/newposte.htm





In article ,
Renee - California wrote:

I need to average this range assuming all cells have a value 0. What's the
correct function to
use???(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+ CZ13+DJ13+DT13+ED13+EN13+EX1
3+FH13+FR13+GB13+GL13+GV13+HF13+HP13+HZ13+IJ13)

Try...

=AVERAGE(IF((MOD(COLUMN(N13:IJ13)-COLUMN(N13),10)=0)*(N13:IJ130),N13:IJ1
3))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Renee - California wrote:

I need to average this range assuming all cells have a value 0. What's the
correct function to
use???(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+ CZ13+DJ13+DT13+ED13+EN13+EX1
3+FH13+FR13+GB13+GL13+GV13+HF13+HP13+HZ13+IJ13)

sorry, lost connection and couldn't find the original post.

"JE McGimpsey" wrote:

See an answer to your previous post.

Please don't post the same problem multiple times. It tends to fragment
any answers you get, and potentially wastes the time of those answering
questions that have already been answered. For tips on using these
groups effectively, see

http://cpearson.com/excel/newposte.htm





In article ,
Renee - California wrote:

I need to average this range assuming all cells have a value 0. What's the
correct function to
use???(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+ CZ13+DJ13+DT13+ED13+EN13+EX1
3+FH13+FR13+GB13+GL13+GV13+HF13+HP13+HZ13+IJ13)

Thanks Domenic -

This returned a Div/0 result. Any Ideas???

I appreciate your time and efforts!!

"Domenic" wrote:

Try...

=AVERAGE(IF((MOD(COLUMN(N13:IJ13)-COLUMN(N13),10)=0)*(N13:IJ130),N13:IJ1
3))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Renee - California wrote:

I need to average this range assuming all cells have a value 0. What's the
correct function to
use???(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+ CZ13+DJ13+DT13+ED13+EN13+EX1
3+FH13+FR13+GB13+GL13+GV13+HF13+HP13+HZ13+IJ13)

Do you have any values 0 in your target columns? If not, AVERAGE() will
return #DIV/0

In article ,
Renee - California wrote:

This returned a Div/0 result. Any Ideas???

Yes. In fact, they should all be 0. These columns represent retail price
at different locations. If there's a zero in the column, it signifies that
this product is not being carried at this particular store.

"JE McGimpsey" wrote:

Do you have any values 0 in your target columns? If not, AVERAGE() will
return #DIV/0

In article ,
Renee - California wrote:

This returned a Div/0 result. Any Ideas???

Perhaps your values were actually entered as Text (a frequent occurrence
when pasting in data from other apps). In an empty cell, type

=ISTEXT(N13)

If it returns TRUE, then you need to coerce the values to be numbers.
One way:

Copy an empty cell. Select the text numbers you wish to convert. Choose
Edit/Paste Special, and select the Values and Add radio buttons. Click
OK.

Don't do this with formulae - it will overwrite the formula.



In article ,
Renee - California wrote:

Yes. In fact, they should all be 0. These columns represent retail price
at different locations. If there's a zero in the column, it signifies that
this product is not being carried at this particular store.

Nope, I got a false return. They're all set up as $ columns for
clarification. A few columns over I'm trying to get placement averages. At
this rate though....

Is there no way to do a countif 0 on all those cells??? If I can get that,
then I can just () the original set, and divide by that countif formula....
right???

"JE McGimpsey" wrote:

Perhaps your values were actually entered as Text (a frequent occurrence
when pasting in data from other apps). In an empty cell, type

=ISTEXT(N13)

If it returns TRUE, then you need to coerce the values to be numbers.
One way:

Copy an empty cell. Select the text numbers you wish to convert. Choose
Edit/Paste Special, and select the Values and Add radio buttons. Click
OK.

Don't do this with formulae - it will overwrite the formula.



In article ,
Renee - California wrote:

Yes. In fact, they should all be 0. These columns represent retail price
at different locations. If there's a zero in the column, it signifies that
this product is not being carried at this particular store.

COUNTIF() requires a contiguous range, so AFIAK, you can't use it for
your purposes.

The confusing thing is why it isn't recognizing the values as numbers
0. You might want to take a look at my test workbook:

ftp://ftp.mcgimpsey.com/excel/renee_demo.xls


In article ,
Renee - California wrote:

Is there no way to do a countif 0 on all those cells??? If I can get that,
then I can just () the original set, and divide by that countif formula....
right???

That's what I was afraid of.

It is confusing. You've set up your demo exactly how my spread is (Except
for all the data in the columns between). Do you think that because there
are text columns, % columns, number columns and blank columns that lie in
between, that this may be causing the confusion??? I'm at a loss.

Thanks for all you help.

"JE McGimpsey" wrote:

COUNTIF() requires a contiguous range, so AFIAK, you can't use it for
your purposes.

The confusing thing is why it isn't recognizing the values as numbers
0. You might want to take a look at my test workbook:

ftp://ftp.mcgimpsey.com/excel/renee_demo.xls


In article ,
Renee - California wrote:

Is there no way to do a countif 0 on all those cells??? If I can get that,
then I can just () the original set, and divide by that countif formula....
right???

Would you like me to take a look at your file? If so, you can email me
at and I'll see if I can pinpoint the problem.

In article ,
Renee - California wrote:

That's what I was afraid of.

It is confusing. You've set up your demo exactly how my spread is (Except
for all the data in the columns between). Do you think that because there
are text columns, % columns, number columns and blank columns that lie in
between, that this may be causing the confusion??? I'm at a loss.

Thanks for all you help.

Figured it out!!!

I took your formula (and the entire row that corresponded) and pasted it
into my spread. Then I began to filter in my columns. As it turns out, a
few columns over (Row R for example) had the #Div/0. This column represented
% of units the item held to the "set". Because the formula you gave me
captured it even though it wasn't using it, it through the error into the
result. Once I put an IF Statement on to Column R to give me a 0% if the
count was 0, the formula worked perfectly.

Thank you so much for all of your help!!!!!! I appreciate all your efforts!!

"JE McGimpsey" wrote:

COUNTIF() requires a contiguous range, so AFIAK, you can't use it for
your purposes.

The confusing thing is why it isn't recognizing the values as numbers
0. You might want to take a look at my test workbook:

ftp://ftp.mcgimpsey.com/excel/renee_demo.xls


In article ,
Renee - California wrote:

Is there no way to do a countif 0 on all those cells??? If I can get that,
then I can just () the original set, and divide by that countif formula....
right???