Help with fixing formulas b/c 0

[theColonel]

What I'm tring to do: I'm trying to create a worksheet that will help with ordering parts for our inventory based off previous years sales of parts. I have 4 years of data I'm basing this off of. The columns with formulas I've written are average order size, order size deviation, high month, average number of orders, average items sold per year, average dollar amount sold per year. All based off the individual years average order size, dollar amount sold, etc.

Problem: The formulas I've written seem to work fine if there was a sale in every month for the part. When there isn't a sale, the zero wreaks havoc on the formulas. I'm fairly new to excel and before going through and trying to include if's in the formulas, I was wondering if there is a way to have formulas ignore cells if there is a zero in them.

I have to present this next friday and would really like to impress. If I wasn't clear enough on the problem or you need more info on the formulas let know and I can post it.
Thanks

 

[vavs]

Are you using the average formula or are you writing your own formula. For example you may get a problem if you use sum(A1:L1)/countif(A1:L1,>0) rather than average(A1:L1). I need to see the formulas to help you out. For the most part, I try to use the average, etc functions that exist.

 

[BenMiller]

Can you post your formulas please? Try using Excel Jeanie.

 

[pplstuff]

One thought to consider, even if there are no unit sold in that period, maybe it's a product that is likely purchased in pairs.

For example, no one buys hot dogs without buns.. (I realize this isn't true, but I believe it illustrates the point well)

You could check to see if there is some *interaction effect* between the products. "If we sell a lot of X, we're more likely to sell some of Y." Identifying these interaction effects might help with some of the issues you mention.

 

[theColonel]

Here is the excel jeanie clipboard set up
http://

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ
2	Item #	Item Description	Price	Cost	Avg Order Size Per Year	Order Size Variance	High Month	Avg # of Orders Per Year	Avg Items Sold Per Year	Avg $ Amount Sold Per Year	Avg Order Size	$ Amount Sold	Total Items Sold	Orders Per Year	High Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Avg Order Size	$ Amount Sold	Total Items Sold	Orders Per Year	High Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Avg Order Size	$ Amount Sold	Total Items Sold	Orders Per Year	High Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Avg Order Size	$ Amount Sold	Total Items Sold	Orders Per Year	High Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	67137	manual input	manual input	1269	#VALUE!	0.0000	#VALUE!	#VALUE!	3.00	3807	#VALUE!	1269	1.00	manual input	Jun	all manual inputs					1.00							#VALUE!	6345	5		#VALUE!		0	5										0	0	0		#N/A													6	7614	6	1	May					6
Sheet1

I think I used excel jeanie correctly if not let me know and i'll retry it. Thanks for the help too by the way. Pplstuff thats a good idea I hadn't thought of, might try and get into that more after I finish it.