2 questions

[mulefan101]

Hello everybody

I’m a new Excel user and maybe the worlds biggest Nerd, I can’t believe how cool this thing is! I have two questions.

# 1 I have a work book for my monthly bills; it has a summery page a first and last page and a page for each month. It works great but I want to average my bills, Cash and such so I need to exclude zeros. I did a search for average exclude 0 but could not get any of those to work in my work book.

# 2 I made an estimating Template for work. I used the same type of approach as above, I put the sheets between a first and last page so I can add as many sheets as I need and send all the totals too the summary page. But what about if I need to bid four-hundred and eighty-two widgets all the same thing? I need a cell that I can enter a number in that will Multiply all my hours, materials and such instead of copying all those sheets ( its a lot easier to think about how long it takes to build one widget than 50 or 382 or whatever). I hope I am not being too detailed in my descriptions but I don’t want to waste your time because I wasn’t clear about what I am looking for.

Joe


P.S Thanks for your help I know some of you make your money by making templates for this kind of stuff.

 

[venkat1926]

try this

=AVERAGE(IF(A1:A11<>0,A1:A11))

fire this formukla with CONTROL+SHIFT+ENER

 

[BarnBrian]

The last reply didn't work for me, try this.

=SUM(A1:A11)/COUNTIF(A1:A11,">0")

BarnBrian

 

[mulefan101]

I tried both ways and I get a # value. here is what my Formula looks like.

thanks



=SUM(First:Last!D16)/COUNTIF(First:Last!D16,">0")

 

[DougStroud]

It appears to me you are trying to do your calculation based on your sheet names as a range, which if you are, I don't believe you can handle it like that.
=SUM(First:Last!D16)/COUNTIF(First:Last!D16,">0")

 

[Smitty]

Welcome to the Board!

See the helpfile for "Functions that can be used in a 3-D reference". COUNTIF isn't one of them, but you can use COUNT or COUNTA.

Hope that helps,

Smitty

 

[mulefan101]

I got it to work thanks guys