Removing the #div/0! from my sheet

[zepan]

Hi Guys and Girls,

I have big sheet with some simple formulas working out the cost price for each unit =SUM(H5)/F5. In some cases the answer is 0 and understand you can't divide 0 by 0 and this is why the remark #DIV/0! comes in the cell. How can I clear this to give me a 0 and not the #div/0! Error.

Thanks in advance,

Matt

 

[Bravo2003]

COUNTIF(S) only counts the number of occurences of a defined criteria in the defined range. In the above formula, if the value is ">0" in the range $D$18:$Z$18 AND/OR if the value in the range $D$18:$Z$18 equals "NM3" then the value is counted. $AB20 is divided by this total count. The rest of the formula manages a #div/0 error when the count is 0. This is apparently not what U want. I'm guessing SUMIF would be better. Maybe start from the beginning.... what & where is your data and what is your outcome objective? It's not clear... month data D:Z has more than 12 months. I really don't understand what the "NM3" is for and/or where it is located? Dave

Ok columns D to Z are as follows :
D = Jan NM3 (QTY)
E = Jan £ (Value)
F = Feb NM3 (QTY)
G = Feb £ (value)
H = Mar NM£...etc etc...
do you get that part?

I need to then take each month that has a NM3 figure in the cell and work out the annual average NM3 value, but only when a figure is there

For example, Jan, June and Sept have 3.5, 4.5 & 3.0 respectively in them - I need 11 (total) divided by 3 (not divided by 12!!!)
A cell will have a value (upto 3 decimal places) either positive or negative (e.g. 4.002 or -3.512), or be blank - but there are the randon 0.00 values in some cells

The columns between (Jan £ (Value)) have £120.35 type values in them - I don't need to do anything with these columns, they just need to be ignored for my required formula above)

Any help?

 

[Bravo2003]

Did U trial my 2nd attempt? Dave

Hi Dave,

Just to update you - this formula, after I had retrialed it, actually worked:
=IF(ISERROR($AB18/COUNTIFS($D18:$Z18,">0",$D$17:$Z$17,"NM3")),0,$AB18/COUNTIFS($D18:$Z18,">0",$D$17:$Z$17,"NM3"))

Thank you for your help.

Cheers

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave