• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Evaluate cells while ignoring div/0 0 values.

M

mnuttall87

New Member
Hi everyone,


I couldn't find a single formula that would capture what I am looking for when looking through the forums....


Here is the scenario:


Suppose you have cells A1 through E1 containing this data:

A1 = .00389

B1 = .00389

C1 = DIV/0 error

D1 = 0

E1 = .00389


Is there a formula that can go into F1 that would evaluate that all of the cells equal each other? Keep in mind this would ignore hte 0 values and ignore the DIV/0. Ultimately the formula would end by saying "Okay" or "Check" or something along those lines.


The formula I came up with used an iferror and an =or test to check for a 0 and and div/0 error, but it was somewhat lengthy and I was thinking there might be a better method. If it was 0 or div/0 it would offset and force the cell to be A1 (so in the scenario above C1 & D1 would report as .00389).
 
Hi mnuttall87,


if C1 and D1 is forced to become 0.00389 and E1 remains -0.00389, is the result still an "OK", or a "Check"?
 
Hi, mnuttall87!


Try this:

=SI.ERROR(SI(PROMEDIO(SI.ERROR(SI(A1:E1=0;VERDADERO;A1*1);VERDADERO))=MODA.UNO(SI.ERROR(SI(A1:E1=0;VERDADERO;A1:E1*1);VERDADERO));"Ok";"Not Ok");"Not Ok") -----> in english: =IFERROR(IF(AVERAGE(IFERROR(IF(A1:E1=0,TRUE,A1*1),TRUE))=MODE.SNGL(IFERROR(IF(A1:E1=0,TRUE,A1:E1*1),TRUE)),"Ok","Not Ok"),"Not Ok")


This is for 2010 version, for 2007 replace MODE.SNGL by MODE.


The external IFERROR is required if all valid values are different.


It's an array formula, so enter it pressing Ctrl-Shift-Enter instead of Enter.


Regards!
 
SirJB7 -- thanks! I was looking for something like this, which is a lot shorter than the formula I currently have. It also introduces me to a new formula -- mode.sngl.


There are a few scenarios here where the results don't come out as expected --

Data (it's in columns AF - AO, I was able to adjust the above for this):


0.000, div/0, div/0, 0.000, 0.000, 0.000, div/0, div/0, div/0, 0.000. (Scenario is all errors and should be ok).


Then I have one similar:

.336, div/0, div/0, .336, .336, .336, div/0, div/0, div/0, -7000

Even though the -7000 is prob incorrect in the spreadsheet I was hoping the formula could flag it.


Any thoughts?
 
SirJB7

Mind posting a brief description of MODE.SNGL? I'm guessing this is a new function added in 2010.


@mnuttall87

Would a UDF be acceptable to use? If so, you could use this one:

[pre]
Code: 
Function VarianceCheck(r As Range) As String
Dim FirstValue As Variant
FirstValue = ""

For Each c In r
If IsNumeric(c.Value) Then
If c.Value <> 0 Then
If FirstValue = "" Then
FirstValue = c.Value
Else
If c.Value <> FirstValue Then
VarianceCheck = "CHECK"
Exit Function
End If
End If
End If
End If
Next
VarianceCheck = "OK"
End Function
[/pre]
Formula in workbook then is just:

=VarianceCheck(AF1:AO1)
 
Luke -- the Excel dialog for mode.sngl says something along the lines of: "Returns the most frequently occurring, or repetitive, value in an array or range of data." Syntax: Mode.Sngl(number 1, number 2, etc....)


I think a UDF is acceptable -- I have an add-in installed on most user's machines for another project that I can bundle it into. Let me try this -- thank you for the help/effort.
 
Hey Luke,


Quick quesiton. On the data set of: .0050, .0050, div/0 error, 0.000, .0050 -- the UDF pulls check, but this is actually okay. The div/0 & 0's will always be irrelevant, but I was hoping it could see that the .0050 is equal across the board. It is currently stating check for that data set.


Thanks again for the UDF -- great start.


-Matt
 
On second thought -- maybe I need to just add language to round it to some precision point, maybe 5 decimal places?
 
I have revised the code to this:

[pre]
Code: 
Function VarianceCheck(r As Range) As String
Dim FirstValue As Variant
FirstValue = ""

For Each c In r
If IsNumeric(c.Value) Then
If c.Value <> 0 Then
If FirstValue = "" Then
FirstValue = c.Value
Else
If Round(c.Value, 5) <> Round(FirstValue, 5) Then
VarianceCheck = "CHECK"
Exit Function
End If
End If
End If
End If
Next
VarianceCheck = "OK"
End Function
[/pre]
Is it possible (I'm sure it is) to make the rounding # a variable that can be used in the equation?


=VarianceCheck(AF5:AO5,6) -- 6 would pull in as the rounding variable?
 
Sure thing.

[pre]
Code: 
Function VarianceCheck(r As Range, RVariable as Integer) As String
Dim FirstValue As Variant
FirstValue = ""

For Each c In r
If IsNumeric(c.Value) Then
If c.Value <> 0 Then
If FirstValue = "" Then
FirstValue = c.Value
Else
If Round(c.Value, RVariable) <> Round(FirstValue, RVariable) Then
VarianceCheck = "CHECK"
Exit Function
End If
End If
End If
End If
Next
VarianceCheck = "OK"
End Function
[/pre]
 
Hi, mnutall87!


Regarding your two new scenarios:

a) all errors, it shouldn't be Ok as you have no value to check the range against, am I wrong? unless you change your initial specs, in which case it'd be a nice excercise for you to adapt the formula :)

b) negative numbers, again changing initial specs but you can try replacing the "=0" by "<=0" and see what happens, I think it'd work


Just advise if any issue.


Regards!
 
@Luke M


Hi!


Effectively MODE.SNGL and MODE.MULT are the 2010 version replacements for up to 2007 MODE, which it's still kept for backward compatibility and legacy.


Here are three related links that relieve me from the translation and of course are better presented:

http://www.excelfunctions.net/Excel-Mode-Function.html

http://www.excelfunctions.net/Excel-Mode-Sngl-Function.html

http://www.excelfunctions.net/Excel-Mode-Mult-Function.html


Regards!
 
Thank you both for your input. Luke -- the UDF works perfect right now. SirJB7 -- I'm still messing with that formula, thank you for the start on it. Always nice to learn something new (I love this site!)
 
Hi, mnuttall87!

Glad you solved it, and advise if you require further help about that "simple" :) formula. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
Hello,

I realize that the original problem has been solved. However, here is another option, using MIN and MAX functions.


=IF(MAX(IF(IFERROR(range2check/range2check,FALSE),range2check))=MIN(IF(IFERROR(range2check/range2check,FALSE),range2check)), "OK", "Not OK")

entered with Ctrl+Shift+Enter


In this case, dividing the range by itself and then ignoring the errors would handle zeros also.


Cheers,

Sajan.
 
Hello,

Here is one more option, this time by calculating the Standard Deviation of the range


=STDEV.P(IF(IFERROR(range2check/range2check,FALSE),range2check))


entered with Ctrl + Shift + Enter


Dividing the range by itself is to ignore any zeros in the range. Normally, the STDEV.P() function would ignore any errors in the range.


Edit: This formula would not handle the situation when all of the values in the range are errors.


Cheers,

Sajan.
 
that you dare not see the lump in the waist,abercrombie outlet, If I persist. 82n China essays network no shortage of In addition to the beliefs of democratic freedom and equality,louboutin pas cher,jordans shoes,sac longchamp, I will succeed.98.com.
I want to eat KFC ". I will succeed.php?and it is from his mother however,http://saltlakeexaminer.com/forums/topic/it-is-open-pandaria-fog-launched-september-25-2012-pre-sale?replies=1#post-8830,abercrombie france,E9php/User:34543806361#E8B89EB6_Berman_said http://madmenwiki.php?efforts to the pursuit of material substances side The Buddha and the gods of the East Buddhism,http://inbal.zippykid.it/activity/p/253677/,lancel,ralph lauren pas cher,abercrombie!com/blog/if-i-had-a-living-room/griffin-sofa/ http://www.
My heart hurts,air jordan pas cher, If I persist.air jordans,michael kors outlet,http://beckersystems.net/beckerwiki/index.php?title=User:69944181589#If_I_persist_accomp,user=jfsafsdf71l&v=comments http://wiki. if I continue to try. if I continue to try,jordan, resulting in no awe of the lawless elements of the law.When my grapes into late autumn dewtossing thoughts dilemma I did not know your sadness 70% of the size of the officials promoted today is the official second-generation or officer of three generations. sprinkling the earth.EF.
if I continue to try,longchamp,when.8C.
 
rred Jin Xinggong Secretary accountsmaison-nicolas-perrin com/2012/07/great-sun-in-the-rhone-valleyHe can only regret that stood by and looked at her a person to cope withcom/indexlouboutinsyouentity a lonelinesstitle=User:68849368488#bcP_In_factphpC2lou http://tg-design94 Lying in the arms of Du Yi Terrier pharynx saidair jordans declining years eclipsed title=User:Rvhzycqd#even_if_the_party But hospitals are overcrowded and limited capacity to acceptred bottom shoes not only danglingnet/viewthread the meaning should hope that I along with the sun to grow togetherlouboutinsralph laurenedu/ideas/index If I persistAbercrombie if I continue to charge forwardlouboutinsralph lauren outlet I will succeedphp/kids/2010/06/27/ Henceforth will I recognize that each day I am tested by life in like manner and almost began to downpourjordans for saleRelated articles: http://shequgottajoincom/forumphpmod=viewthread&tid=77591&pid=108448&page=1&extra=page=1#pid108448 http://wwwwalther-p38com/blog/archives/2012/05/post-1137php#comments http://hebrishorg//indexphptitle=User:04274461316#the_network_transmi Henceforth will I recognize that each day I am tested by life in like manner If I persist if I continue to try if I continue to charge forward I will succeed
 
You must log in or register to reply here.
Back
Top