Replies

[TABLE]
[TR]
[TD]Or try this shorter formula :[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]=MID(A1&” “&A1,FIND(“,”,A1)+2,LEN(A1)-1)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Regards[/TD]
[/TR]
[/TABLE]

…….I am trying to test or apply a logical test to a column of numbers, such that if any cell value in the column is outside the numerical value range of “2.5 to 3.5” a 1 is returned, if any cell value is within this range then a 0 is returned……….
Marty

1] In excel, OR() is equal to ” + ” ( plus sign of arithmetic operator )

2] Check if a number is greater than or less than another number

=(E1<2.5) will return TRUE or FALSE

3] Thus, a logical testing for numeric value outside the range of "2.5 to 3.5"

The formula : ( without IF() and OR() )

=(E13.5)

Regards
Bosco

While your solution works with numbers entered in A1:A2; if text is entered in either cell an error is returned. The solution offered by Hans “=IF(ISERROR(A1/A2),0,A1/A2)” does not succumb to this trap.

But, the OP asked for : ” How to return zero when computation in 100 / 0 ? ( or A1/A2 )”

I think the cell A1:A2 ( 100 / 0 ) were numbers

The ISERROR() definite not required

Regards
Bosco

Please refered to this Thread #12, regarding blank cell and formula blank cell

Blank Excel Cells

Regards
Bosco

Is there a function with in Excel that returns zero for a undefined computation such as in 100 / 0?
John

Or try this,

can be used in all Excel versions and without ISERROR testing function :

=IF(A2,A1/A2,0)

Regards
Bosco

But the “formula blank cell” A4 is not included in the count!

Hi Hans,

Oh!… my mistake!…..the mistake was corrected as per your advised.

Thank you for your good catch

Regards
Bosco

……While using the “COUNTA and COUNTBLANK” functions to count the number of cells within the same column that contain, a 1, a 0 or are blank, the COUNTBLANK function returns a correct value, those cells that are blank. Yet the COUNTA function merely counts the total number of cells within the specified range, whether a 1, 0 or blank.
Marty

Herein the examples in explanation of using COUNT, COUNTA, COUNTBLANK and COUNTIF

Testing datas at Column A1:A5

……Col A…
..1….M……
..2….8……
..3…. …… ( A3 is a blank cell )
..4…. …… ( A4, is a formula blank cell, with enter : =”” )
..5….9……

1] COUNTBLANK…..Count no of blank cells (blank and formula blank cells)

=COUNTBLANK(A1:A5)

=2 (A3+A4)

2] COUNTA…..Count no of non-blank cells, include formula blank cell

=COUNTA(A1:A5)

=4 (A1+A2+A4+A5)

3] COUNT…..Count no of numeric cells, exclude formula blank cell

=COUNT(A1:A5)

=2 (A2+A5)

4] =COUNTIF…..Count no of text cells, include formula blank cell

=COUNTIF(A1:A5,”*”)

=2 (A1+A4)

5] =COUNTIF…..Count no of text cells, exclude formula blank cell

=COUNTIF(A1:A5,”?*”)

=1 (A1)

Remark : COUNTIF is a conditional count function, please refer to the Help File for further information

Hope can help

Regards
Bosco

You can also use =”” in the place of ISBLANK
=IF(E2=””,””,IF(OR(E23.5)=TRUE,1,0))

Or, using only one IF function is enough

=IF(E2=””,””,(E23.5))

Regards
Bosco

=IF(A2=”ABC”,”ABC “,”Others “)&IF(OR(G2={“A999″,”B999″,”C999″,”999A”,”999B”,”999C”}),”AUTO “,IF(OR(G2={“1 India”,”2 India”,”India 1″,”India 2″,”USA”}),”MANUAL “,””))&IF(M2=”No”,”CORRECT”,IF(M2=”Yes”,”INCORRECT”))

Use ” Text to Columns ” way :

Select all column C “dates” >> Format Cell >> Number >> Date >> and choose : 2001-03-14 >> OK

>> Data >> Text to Columns >> Delimited >> Next >> Next >> choose : Date >> select : YMD form the dropdown list >> Finish

Regards
Bosco

Or, try…………….

=LOOKUP(H2,A$2:D$61)

Regards
Bosco

Or, try to use this shorter formula :

=REPLACE(CELL(“filename”,A1),1,FIND(“]”,CELL(“filename”,A1)),)

Regards
Bosco

In follow Hans’ table :

1] The Min value of each year ( Helper column ), H3 enter the array formula and copied down :

{=MIN(IF(–TEXT($C$3:$C$47,”yyyy”)=G3,$D$3:$D$47))}

2] The cell address ( Row number ) of each year’s minimum value, I3 enter the array formula and copied down :

{=MAX((–TEXT($C$3:$C$47,”yyyy”)=G3)*($D$3:$D$47=H3)*ROW($3:$47))}

3] Date of each year’s minimum value, J3 enter the formula and copied down :

=INDEX(C:C,I3)

Regards
Bosco

Or this non-array formula :

=SUMPRODUCT((Sheet1!B1:B10={“red”,”blue”,”green”})*Sheet1!A1:A10)

Regards
Bosco

Try this formula in B12:
=IF(OR($C2=”Saturday”,$C2=”Sunday”),B2,IF(OR($E2TIME(17,0,0)),B2,””))

Or, shorten to :

=IF((LEFT($C2)=”S”)+(($E2TIME(17,,))),B2,””)

Regards
Bosco