# Search:

Type: Posts; User: Scott Huish; Keyword(s):

Page 1 of 6 1

1. ## Thread: If Statment Help

by Scott Huish
Replies
8
Views
161

### Re: If Statment Help

Like this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...
2. ## Thread: If Statment Help

by Scott Huish
Replies
8
Views
161

### Re: If Statment Help

Perhaps:
=IF(AND(A2<>"",C2<>""),A2&" "&REPLACE(C2,1,FIND(" ",C2),""),IF(C2=C1,B1,""))

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border:...
3. ## Thread: Finding row number of the last date in a given month

by Scott Huish
Replies
3
Views
178

### Re: Finding row number of the last date in a given month

That won't work because the amount of dates in May is greater than the largest value in that sample.

Perhaps:
=LOOKUP(9.99999999E+307,IF(TEXT(B2:B5,"mm/yyyy")="05/2019",B2:B5),ROW(B2:B5))
...
4. ## Thread: A better formula?

by Scott Huish
Replies
7
Views
335

### Re: A better formula?

More specifically, if it contains a non-zero number, it would return 1. If it contained 0, it would return 0.
5. ## Thread: A better formula?

by Scott Huish
Replies
7
Views
335

### Re: A better formula?

Yes, if a number is in that spot, non-zero is TRUE and zero is FALSE.
6. ## Thread: VBA to extract variable length number, following a variable character

by Scott Huish
Replies
17
Views
471

### Re: VBA to extract variable length number, following a variable character

=LOOKUP(9.999999999E+307,MID(A2,FIND(".",A2)+1,ROW(INDIRECT("1:15")))+0)

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px...
7. ## Thread: A better formula?

by Scott Huish
Replies
7
Views
335

### Re: A better formula?

You could shorten it a little bit by writing it like this:
=IF(A2*B2,C2/ABS(B2),SIGN(C2))

by Scott Huish
Replies
4
Views
98

### Re: Formula Help

=MEDIAN(10,A1,100)
9. ## Thread: Nested formula problem

by Scott Huish
Replies
9
Views
241

### re: Nested formula problem

You shouldn't put numbers in quotes, that makes them text.
=IF(AND(X3<15,X3>7),1,IF(AND(X3<80,X3>0),2,IF(AND(X3<0,X3>-3),3,IF(AND(X3<-3,X3>-3),4,"N"))))

Your last choice doesn't make sense though...
10. ## Thread: SUMPRODUCT error when blank cell

by Scott Huish
Replies
8
Views
333

### Re: SUMPRODUCT error when blank cell

Ok, but blanks still evaluate to 0. Do you mean they're not actually blank, as in empty string "" or space " " or what?

As you can see, it's not failing as you have described the problem:
...
11. ## Thread: Nested formula problem

by Scott Huish
Replies
9
Views
241

### re: Nested formula problem

I would check the entire range at once:

=IF(COUNTIFS(X3:AB3,">2",X3:AB3,"<4")=5,"Yes","No")
12. ## Thread: SUMPRODUCT error when blank cell

by Scott Huish
Replies
8
Views
333

### Re: SUMPRODUCT error when blank cell

If columns B & E are actually empty, that would equate to 0 in your formula. So what is your problem exactly?
13. ## Thread: Countifs specific word in column a and if specific word in column b

by Scott Huish
Replies
2
Views
173

### Re: Countifs specific word in column a and if specific word in column b

Yes. But your syntax is wrong, there is no AND IF statement in that. I'm in the US so I'm using commas instead of semicolons.

=COUNTIFS('2019-2020 LIST '!\$I\$2:\$I\$76,"NOT DEFINED", '2019-2020 LIST...
14. ## Thread: how to extract a string of numbers from a cell containing text and numbers

by Scott Huish
Replies
6
Views
142

### Re: how to extract a string of numbers from a cell containing text and numbers

Here's a formula solution:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("RE",A1&"RE"),255)," ",REPT(" ",255)),255))

by Scott Huish
Replies
6
Views
268

### Re: msgbox question

Unless you pass the value to a variable, in which case you need the parentheses:

c= MsgBox ("Some text", vbYesNo)

by Scott Huish
Replies
6
Views
268

### Re: msgbox question

It will change the variable to the case you used when you dimmed the variable.

There are cases when the parentheses are required such as when you are assigning the results of a msgbox to variable...
17. ## Thread: Nested IF x50

by Scott Huish
Replies
2
Views
174

### Re: Nested IF x50

Without much else to go on, I would suggest looking at the VLOOKUP function.
18. ## Thread: Getting Cell Data Based on Boolean Values

by Scott Huish
Replies
2
Views
155

### Re: Getting Cell Data Based on Boolean Values

Try this UDF:

Function TrueValues(r As Range)
Dim c As Range, s As String
For Each c In r.Offset(, 1).Resize(, 1)
If c Then s = s & c.Offset(, -1) & ", "
Next
If Len(s) Then s = Left(s,...
19. ## Thread: Excel VBA InStr Function to delete string of text within a cell

by Scott Huish
Replies
5
Views
523

### Re: Excel VBA InStr Function to delete string of text within a cell

Yes, not all your cells in that range contain that text, try this:

Sub TUR()
Dim c As Range
For Each c In Worksheets("Data").Range("AK2:AK" & Range("AK" & Rows.Count).End(xlUp).Row)
c =...
20. ## Thread: Excel VBA InStr Function to delete string of text within a cell

by Scott Huish
Replies
5
Views
523

### Re: Excel VBA InStr Function to delete string of text within a cell

I'm assuming you're just trying to return the number other than the part that starts with TUR. Is your number after TUR always 8 digits, if so:

Sub TUR()
Dim c As Range
For Each c In...
21. ## Thread: Rounding using ceiling with conditions

by Scott Huish
Replies
9
Views
284

### Re: Rounding using ceiling with conditions

Try this:
=MAX("1:15"+0,CEILING(ROUND(B2-A2,2),"00:15"+0))
22. ## Thread: Rounding using ceiling with conditions

by Scott Huish
Replies
9
Views
284

### Re: Rounding using ceiling with conditions

=max("1:15"+0,ceiling(b2-a2,"00:15"+0))
23. ## Thread: Formula to extract 5 or 6 digits based on the text

by Scott Huish
Replies
11
Views
307

### Re: Formula to extract 5 or 6 digits based on the text

A different way and this won't error if there's no colon:

=TRIM(REPLACE(A1,1,FIND(":",A1&":"),""))
24. ## Thread: Convert Text String to Single Letter Range/Single Number Range?

by Scott Huish
Replies
1
Views
101

### Re: Convert Text String to Single Letter Range/Single Number Range?

The first thing I would suggest is to stop putting parentheses around things that don't need it. That is very hard to read.

What do you mean by active range exactly, that it's selected or what?
...
25. ## Thread: Subtracting Same Dates getting -1

by Scott Huish
Replies
2
Views
199

### Re: Subtracting Same Dates getting -1

Can you post sample data of where this is not working as you expect and the formula you are using?
Results 1 to 25 of 150
Page 1 of 6 1