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

 

[NdNoviceHlp]

To begin with, remove the quote marks around the zero... otherwise the outcome is text and can't be calculated which will probably cause some bizzaro error later on. How can last weeks results not equal the previous this weeks results? -100% is impossible...U haven't made/sold one of whatever this week to compare to last week's sales, hence you're outcome is zero as there is nothing to compare this week to last week. Logical but I'm guessing still wrong for U. It seems on a gut level that, for example if U made 10 last week, and U usually make 10 every week, as a standard that U could use that 10 standard/average to compare your actual amount made this week vs that standard/average 10 amount. For example, If U made 7 this week then U didn't make 3 this week. If U had made 1 this week then U didn't make 9 this week and were then at -90% of the standard. If U made .01 then maybe -99.99% of the standard... but at zero there is still nothing to compare and -%100 is still impossible. If your still keen on a solution, it doesn't seem that difficult. Dave

 

[tubby]

I'm having the same issue. I'm not sure how to convert my formula to the correct one so it won't appear on the graph and throw off my scale.

=(C41/B41)*100

The commas are throwing me for a loop. I'm not sure of their purpose.

 

[NdNoviceHlp]

The "If" function works like this... If(some condition is met, do something if condition is met, else do something if condition is not met) Note that the commas separate the condition and the 2 possible outcomes. If I remember right N/A values are not charted. So maybe...

=IF(IsError((C41/B41)*100),N/A(),(C41/B41)*100)

HTH. Dave

 

[Bravo2003]

Hi Dave,
Sorry to ask the same old question again, but I have tried to solve this myself and failing!! Brain aches now with formulas!!

Current formula
=AB18/COUNTIFS(D18:Z18,">0",D$17:Z$17,"NM3")

Need to avoid any #Div/0! errors and just have 0 in place.

Thanking you much in advance..

JC[TABLE="width: 64"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[NdNoviceHlp]

Not exactly sure what U want. The COUNTIF function (Not COUNTIFS) has a range and a criteria. So if U want to count the values greater than zero in range D18:Z18...

=COUNTIF(D18:Z18,">0")

If there are none then AB18/COUNTIF(D18:Z18,">0") will produce a #Div/0 error which can be avoided by the following use of IsError. I have no idea what U are trying to do with the "NM3"? HTH. Dave

=IF(ISERROR(AB18/COUNTIF(D18:Z18,">0")),0,AB18/COUNTIF(D18:Z18,">0"))

Whoops... apparently my 03 version has no COUNTIFS, I'll trial this again

 

[NdNoviceHlp]

Trial this...

=IF(ISERROR(AB18/COUNTIFS(D18:Z18,">0",D17:Z17,"NM3")),0,AB18/COUNTIFS(D18:Z18,">0",D17:Z17,"NM3"))

Dave

 

[Bravo2003]

Not exactly sure what U want. The COUNTIF function (Not COUNTIFS) has a range and a criteria. So if U want to count the values greater than zero in range D18:Z18...

=COUNTIF(D18:Z18,">0")

If there are none then AB18/COUNTIF(D18:Z18,">0") will produce a #Div/0 error which can be avoided by the following use of IsError. I have no idea what U are trying to do with the "NM3"? HTH. Dave

=IF(ISERROR(AB18/COUNTIF(D18:Z18,">0")),0,AB18/COUNTIF(D18:Z18,">0"))

Whoops... apparently my 03 version has no COUNTIFS, I'll trial this again

Thanks for helping Dave.
I've tried this:
=IF(AB349=0,0,AB177/COUNTIFS(D177:Z177,">0",D$17:Z$17,"NM3"))
which works for 98% of the issues, but now problems on negative values (e.g. -2.27/-591) this one works, but -3.23/-888 doesnt work and gives Div error?
also there are still 0.0/0 's giving div error??

Now I'll explain what the formula is trying to do to help if it does....
D177 to Z177 contains months jan-dec 1 column NM3 values next column £value, so the "NM3" in formula is asking to only count these columns to give an average but formula is asking to only count cells that contain a value and is not 0

if that makes sense? (I need to find the average NM3 based on only the months that contain sales.)

 

[NdNoviceHlp]

Did U trial my 2nd attempt? Dave

 

[Bravo2003]

Did U trial my 2nd attempt? Dave

=IF(ISERROR($AB20/COUNTIFS($D$18:$Z$18,">0",$D$17:$Z$17,"NM3")),0,$AB20/COUNTIFS($D$18:$Z$18,">0",$D$17:$Z$17,"NM3"))

Just gives me a total, not the average?

 

[NdNoviceHlp]

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