Help with fixing formulas b/c 0

theColonel

New Member
Joined
May 31, 2012
Messages
9
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
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.
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top