# Search:

Type: Posts; User: bosco_yip; Keyword(s):

Page 1 of 5 1

1. ## Thread: Rearrange Smith, John to John Smith in Excel

by bosco_yip
Replies
3
Views
507

### Or try this shorter formula : ...

Or try this shorter formula :

=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)
2. ## Thread: syntax for logical tests

by bosco_yip
Replies
9
Views
575

### 1] In excel, OR() is equal to " + " ( plus sign...

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...

by bosco_yip
Replies
4
Views
412

### But, the OP asked for : " How to return zero when...

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
4. ## Thread: Truly Empty Cells

by bosco_yip
Replies
8
Views
611

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

Blank Excel Cells

Regards
Bosco

by bosco_yip
Replies
4
Views
412

### Or try this, can be used in all Excel...

Or try this,

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

=IF(A2,A1/A2,0)

Regards
Bosco
6. ## Thread: Blank Excel Cells

by bosco_yip
Replies
13
Views
706

### Hi Hans, Oh!... my mistake!.....the mistake...

Hi Hans,

Thank you for your good catch

Regards
Bosco
7. ## Thread: Blank Excel Cells

by bosco_yip
Replies
13
Views
706

### Herein the examples in explanation of using...

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...
8. ## Thread: Blank Excel Cells

by bosco_yip
Replies
13
Views
706

### Or, using only one IF function is enough ...

Or, using only one IF function is enough

=IF(E2="","",(E2<2.5)+(E2>3.5))

Regards
Bosco
9. ## Thread: USING Multiple if and OR

by bosco_yip
Replies
5
Views
347

### =IF(A2="ABC","ABC ","Others...

=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...
10. ## Thread: convert date from texte but in antother format

by bosco_yip
Replies
2
Views
263

### Use " Text to Columns " way : Select all...

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 :...

by bosco_yip
Replies
2
Views
321

### View Post

Or, try…………….

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

Regards
Bosco
12. ## Thread: Formula to insert worksheet name

by bosco_yip
Replies
8
Views
1,584

### Or, try to use this shorter formula : ...

Or, try to use this shorter formula :

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)

Regards
Bosco
13. ## Thread: date of minimum value

by bosco_yip
Replies
6
Views
753

### In follow Hans' table : 1] The Min value of...

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))}

14. ## Thread: SUMIF with an OR argument

by bosco_yip
Replies
6
Views
422

### Or this non-array formula : ...

Or this non-array formula :

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

Regards
Bosco

by bosco_yip
Replies
10
Views
586

### Or, shorten to : ...

Or, shorten to :

=IF((LEFT(\$C2)="S")+((\$E2<TIME(8,,))+(\$E2>TIME(17,,))),B2,"")

Regards
Bosco
16. ## Thread: Individual To Subject Count (Calc Help)

by bosco_yip
Replies
6
Views
412

### Another way, wihout using helper column, Cell...

Another way, wihout using helper column,

Cell J8, enter the formula and copy down :

=SUMPRODUCT((F\$2:F\$349=I8)*(MATCH(F\$2:F\$349&A\$2:A\$349,F\$2:F\$349&A\$2:A\$349,)=ROW(F\$2:F\$349)-1))

Regards...
17. ## Thread: Removing spaces using trim command

by bosco_yip
Replies
3
Views
365

### View Post

Try, F2……

=LEFT(TRIM(E2),FIND(" ",TRIM(E2)))

Regards
Bosco
18. ## Thread: compare two lists

by bosco_yip
Replies
11
Views
410

### I have list A and list B, some items in list A...

I have list A and list B, some items in list A are the same as those in list B. How can I extract the ones that do not match?

Two (2) ways :

1] With helper column, Column E

1.1) Way A
...

by bosco_yip
Replies
12
Views
601

### =IF((E6="Yes")*(E8="Yes")*(E16="Yes"),"Accepted","...

=IF((E6="Yes")*(E8="Yes")*(E16="Yes"),"Accepted","Rejected")

Regards
Bosco

by bosco_yip
Replies
4
Views
317

### =IF(N(P35)*COUNTA(\$D\$35:\$O\$35),P35/COUNTIF(\$D\$35:\$...

=IF(N(P35)*COUNTA(\$D\$35:\$O\$35),P35/COUNTIF(\$D\$35:\$O\$35,">0"),"")

Regards
Bosco
21. ## Thread: Sorting in Excel

by bosco_yip
Replies
7
Views
379

### =TEXT(A1,"m") Regards Bosco

=TEXT(A1,"m")

Regards
Bosco

by bosco_yip
Replies
6
Views
417

### =SUMPRODUCT(ISTEXT(F10:F14)*(F10:F14"")) ...

=SUMPRODUCT(ISTEXT(F10:F14)*(F10:F14<>""))

=COUNTIF(F10:F14,"?*")

Regards
Bosco
23. ## Thread: sumif with multiple sheets (EXcel 2003)

by bosco_yip
Replies
12
Views
2,671

### Pl see the attachment. Instead of entering...

Pl see the attachment. Instead of entering formula for each sheet, is there a way to minimize the sumif with multiple sheet?

Sheet1
___|____A____]____B____|____C_____|____D____|...
24. ## Thread: Excel 2007 - Separating names formula

by bosco_yip
Replies
2
Views
714

### I have don Adams in cell A3. I need a formula...

I have don Adams in cell A3. I need a formula in cell B3 show me Don. and cell C3 show me Adams.

Another option :

B3 =MID(A3,1,FIND(" ",A3)-1)

C3 =MID(A3,FIND(" ",A3)+1,99)

Or