# Search:

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

Page 1 of 4 1

1. ## Thread: SUM can find a total of up to 30 arguments (Excel 2002)

by maxflia10
Replies
7
Views
519

### Re: SUM can find a total of up to 30 arguments (E

Insert two new worksheets and name them First and Last. Insert ALL relevant sheets between the two and use,

=SUM(First!:Last!B29)
2. ## Thread: COUNTIF function (2000/3)

by maxflia10
Replies
3
Views
289

### Re: COUNTIF function (2000/3)

=SUMPRODUCT((\$A\$2:\$A\$27-DAY(\$A\$2:\$A\$27)+1=\$F2)+0,(\$B\$2:\$B\$27=RIGHT(F\$1,1)+0)

Where F2 houses the first day of the month
3. ## Thread: Counting unique items (2002)

by maxflia10
Replies
9
Views
479

### Re: Counting unique items (2002)

Try,

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

=COUNTDIFF(A1:A100)
4. ## Thread: Missing number from list (Excel 2003)

by maxflia10
Replies
4
Views
772

### Re: Missing number from list (Excel 2003)

Caveat, it doesn't take into account the grouping of 24 as I came up with 2531 and 2535

In B1, enter =MIN(A1:A360)
In B2, enter...
5. ## Thread: Sum (array) v Sumproduct (2002 sp3)

by maxflia10
Replies
4
Views
324

### Re: Sum (array) v Sumproduct (2002 sp3)

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

See,

http://www.decisionmodels.com/optspeedk.htm
6. ## Thread: Average Formula Problem (Excel 2003)

by maxflia10
Replies
4
Views
413

### Re: Average Formula Problem (Excel 2003)

Try array entered,

=AVERAGE(IF(B3:B96="george",A3:A96))

=AVERAGE(IF((B3:B96="george")*ISNUMBER(A3:A96),A3:A96))

I don't see 0 values only blanks
7. ## Thread: GET RID OF #DIV/0 (XP & 2003)

by maxflia10
Replies
3
Views
230

### Re: GET RID OF #DIV/0 (XP & 2003)

John,

Could the N function be used in place of ISNUMBER?
8. ## Thread: the offset function (Excel 2003)

by maxflia10
Replies
8
Views
902

### Re: the offset function (Excel 2003)

An alternative to OFFSET and COUNTA,

=\$A\$1:INDEX(\$A\$1:\$A\$65536,MATCH(9.99999999999999E+307,\$A\$1:\$A\$65536))

for numeric data

=\$A\$1:INDEX(\$A\$1:\$A\$65536,MATCH(REPT("z",255),\$A\$1:\$A\$65536))
...
9. ## Thread: #N/A's in cells (Excel 2000)

by maxflia10
Replies
6
Views
400

### Re: #N/A's in cells (Excel 2000)

Try,

=SUMIF(A1:A100,"<>#N/A")

or rid the cells of the #N/A and just use SUM.
10. ## Thread: IF Problem (Excel 2002)

by maxflia10
Replies
9
Views
305

### Re: IF Problem (Excel 2002)

=VLOOKUP(B7,CHOOSE(MATCH(D5,{"T1","T2","T3","T4","T5","T6","T7","T8"},0),T1,T2,T3,T4,T5,T6.T7.T8),2,0))

Where T1, T2 etc are table names.
11. ## Thread: ? function to count unique similar to CountA( )? (Excel 2003)

by maxflia10
Replies
3
Views
331

### Re: ? function to count unique similar to CountA(

Some options,

=SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))

=COUNTDIFF(A1:A30)

For numeric only
12. ## Thread: Help on Formula (Excel 2003)

by maxflia10
Replies
18
Views
538

### Re: Help on Formula (Excel 2003)

Thank-you to both you and Steve!
13. ## Thread: Help on Formula (Excel 2003)

by maxflia10
Replies
18
Views
538

### Re: Help on Formula (Excel 2003)

Is this syntax correct?

=IF(ISERROR(vlokup(A1,table,)),"",VLOOKUP(A1,table,))
14. ## Thread: Help on Formula (Excel 2003)

by maxflia10
Replies
18
Views
538

### Re: Help on Formula (Excel 2003)

Steve,

See if Excel accepts this formula, without a named range named table and vlokup

=IF(ISERROR(vlokup(A1,table,2,0)),"",VLOOKUP(A1,table,2,0))
15. ## Thread: Help on Formula (Excel 2003)

by maxflia10
Replies
18
Views
538

### Re: Help on Formula (Excel 2003)

As an aside, Vlookup will only return a #N/A error for no exact match and #REF for the column to return which is not in the range. Use ISNA instead of ISERROR if you must. ISERROR will also mask...
16. ## Thread: VLOOKUP ISSUE (XP & 2003)

by maxflia10
Replies
3
Views
231

### Re: VLOOKUP ISSUE (XP & 2003)

Why not clean up your data in the lookup column by doing one of the following,

Run ASAP Utilities at

www.asap-utilities.com

Run this macro,

Sub TrimALL()
'David McRitchie 2000-07-03 mod...
17. ## Thread: SUM based on MAX condition (2000)

by maxflia10
Replies
10
Views
400

### Re: SUM based on MAX condition (2000)

couple other options,

A2:

1-June-05 (or 6/1/2005)

B2:

=EOMONTH(A1,0)
18. ## Thread: Find value based on column/row intersect (Lookup?) (2002)

by maxflia10
Replies
5
Views
1,127

### Re: Find value based on column/row intersect (Lookup?) (2002)

I don't know if one is more efficient than the other. OFFSET is a volatile function, whereas Index/Match is not. What you can do to eliminate a couple of function calls is to enter the column/row...
19. ## Thread: Find value based on column/row intersect (Lookup?) (2002)

by maxflia10
Replies
5
Views
1,127

### Re: Find value based on column/row intersect (Lookup?) (2002)

=INDEX(B2:J9,MATCH(--LEFT(N2,1),A2:A9,0),MATCH(--RIGHT(N2,1),B1:J1,0))
20. ## Thread: Concatenate a range (Excel 2002)

by maxflia10
Replies
10
Views
994

### Re: Concatenate a range (Excel 2002)

=MCONCAT(C3:CP3," ")
21. ## Thread: Counting total numbers (Excel 2003)

by maxflia10
Replies
7
Views
452

### Re: Counting total numbers (Excel 2003)

List your sheet names ina range and name that range. In this example, I've named it "sheets" without the quotes. Then use,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!B3:B100"),2))

Another...
22. ## Thread: converting number to time (excel)

by maxflia10
Replies
16
Views
571

### Re: converting number to time (excel)

Ahhhh....minutes and seconds...I apologize for not reading more carefully!
23. ## Thread: converting number to time (excel)

by maxflia10
Replies
16
Views
571

### Re: converting number to time (excel)

Another option,

=TEXT(A1,"00:00")+0

And format as time.

Are these elapsed times? Is there any time you would want 7:49 PM or 19:49?
24. ## Thread: Conditional Formats (XL2003)

by maxflia10
Replies
7
Views
318

### Re: Conditional Formatting (Pt. 2)

In addition to Han's solution, you can also "coerce" text time/date.

=c2>"3:00"+0
25. ## Thread: Dynamic Ranges /Sorting (XL2003)

by maxflia10
Replies
5
Views
285

### Re: Dynamic Ranges /Sorting (XL2003)

Depending whether the columns houses text or numeric,

=\$B\$6:INDEX(\$B:\$B,MATCH(REPT("z",255),\$B:\$/forums/images/smilies/cool.gif) for text
...
Results 1 to 25 of 89
Page 1 of 4 1