# Search:

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

Page 1 of 6 1

1. ## Thread: need help with formula to make cell a negative value based on values on other cells

by kweaver
Replies
1
Views
76

### Re: need help with formula to make cell a negative value based on values on other cells

Sub FixM()
Dim LR As Long, i As Integer
LR = Cells(Rows.Count, "M").End(xlUp).Row
For i = 1 To LR
If IsEmpty(Range("S" & i)) And IsEmpty(Range("Z" & i)) Then
Else
Range("M" & i) = Range("M"...
2. ## Thread: Formula for 4 for 3

by kweaver
Replies
4
Views
56

### Re: Formula for 4 for 3

=sum(a1:a4)-min(a1:a4)
3. ## Thread: formula to sum hours if

by kweaver
Replies
5
Views
113

### Re: formula to sum hours if

In my formula, if Initial shutdown is 23:30 and there is no initial start-up, it shows -23.5. (can you "shut down" without a "start-up"?)
4. ## Thread: cap Excel 2010 rows and columns in new spreadsheets

by kweaver
Replies
7
Views
135

### Re: cap Excel 2010 rows and columns in new spreadsheets

One thought is to right click on the sheet tab and View Code.
Then, F4 shows the Properties (if not displayed).
Scroll down to "ScrollArea" and enter something like: \$A\$1:\$Z\$100

If they're...
5. ## Thread: formula to sum hours if

by kweaver
Replies
5
Views
113

### Re: formula to sum hours if

I think the columns were a bit off. Is this correct?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse;...
6. ## Thread: Count # of Sequences in a range

by kweaver
Replies
8
Views
102

### Re: Count # of Sequences in a range

And, what would you expect the averages to be in each case of your new sample?
7. ## Thread: Count # of Sequences in a range

by kweaver
Replies
8
Views
102

### Re: Count # of Sequences in a range

Try 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:...
8. ## Thread: Count # of Sequences in a range

by kweaver
Replies
8
Views
102

### Re: Count # of Sequences in a range

Is the average for Unit 2 = 1.2333?
9. ## Thread: VBA trim data with space not from spacebar

by kweaver
Replies
4
Views
39

### Re: VBA trim data with space not from spacebar

Are there always 6 characters then the Oracle character, whatever it is?

Why not just use LEFT to take the left-most 6 or use SUBSTITUTE to change that code to whatever else you wan it to be?
10. ## Thread: SUM ignoring text fields in a non-string of cells

by kweaver
Replies
13
Views
165

### Re: SUM ignoring text fields in a non-string of cells

And, why doesn't =SUM(B:B) work?
11. ## Thread: Running Numbers Formula

by kweaver
Replies
7
Views
200

### Re: Running Numbers Formula

Are you trying to increment the AD-nn ID in the next row?

such as this in A4:

or
12. ## Thread: SUM ignoring text fields in a non-string of cells

by kweaver
Replies
13
Views
165

### Re: SUM ignoring text fields in a non-string of cells

Are you trying to find the sum of the Day1 column for all users or for each one?
Since SUM ignores text, why doesn't =SUM(B:B) work if the DAY column is column B?

If this is not it, you need to...
13. ## Thread: If Or formula

by kweaver
Replies
4
Views
48

### Re: If Or formula

From: =IF(OR(ISNUMBER(MATCH(A6,'Summary Source'!\$E:\$E,0)),ISNUMBER(MATCH(A6,'Summary Source'!\$F:\$F,0))),0,N3)

IF the match in column E is a number OR if the match in column F is a number, then the...
14. ## Thread: Average numbers and ignore text

by kweaver
Replies
6
Views
114

### Re: Average numbers and ignore text

You're welcome. However, I caution you about this because you're testing it with really a "fake" number using Jan. 1, 2019 as the number.
A much more reliable way would be to use the UDF and a...
15. ## Thread: Average numbers and ignore text

by kweaver
Replies
6
Views
114

### Re: Average numbers and ignore text

If you don't mind a helper column and a UDF:

Function idate(rng As Range) As Boolean
idate = IsDate(rng)
End Function

(using it in column D in my example)

Then,
16. ## Thread: Average numbers and ignore text

by kweaver
Replies
6
Views
114

### Re: Average numbers and ignore text

To the best of my knowledge, there's no function to determine which cells in a range are (or aren't) dates.
If you KNOW that all of your legit numbers are smaller than the date number for January 1,...
17. ## Thread: Extract informations if distance (lat,long) between two lists of places is lower than 50km

by kweaver
Replies
3
Views
127

### Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

I did a distance calculation some time ago for zip codes in the US (the UK database of post code is too large for Excel).
My calculations were based on distance "as the crow flies" not as you drive...
18. ## Thread: Highlight duplicate values

by kweaver
Replies
4
Views
101

### Re: How can I highlight duplicate values

Of, adding to Fluff, if you want the word in E:

=IF(COUNTIFS(\$A:\$A,\$A1,\$B:\$B,\$B1,\$C:\$C,\$C1,\$D:\$D,\$D1)>1,"DUPLICATE","")
19. ## Thread: Separate out first and last name

by kweaver
Replies
11
Views
238

### Re: Separate out first and last name

You can also get the last name from your structure using this (ugly) formula:

=LEFT(IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<2,A1,RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1,"...
20. ## Thread: Separate out first and last name

by kweaver
Replies
11
Views
238

### Re: Separate out first and last name

Hamedo89: in your last post, you pull the middle name (or initial), not the last name, if there isn't a Jr., etc.
21. ## Thread: Separate out first and last name

by kweaver
Replies
11
Views
238

### Re: Separate out first and last name

I did the following with some helper columns:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...
22. ## Thread: Separate out first and last name

by kweaver
Replies
11
Views
238

### Re: Separate out first and last name

Since you have the newest version of Excel, have you tried Flash Fill?
23. ## Thread: Separate out first and last name

by kweaver
Replies
11
Views
238

### Re: Separate out first and last name

Nordicrx8:

A few things to try:

Chip Person: http://www.cpearson.com/Excel/FirstLast.htm

I wrote up some stuff:
...
24. ## Thread: how does mod work with negative numbers?

by kweaver
Replies
3
Views
66

### Re: how does mod work with negative numbers?

Excel's MOD function is defined as: MOD(n, d) = n - d*INT(n/d)

When d is negative, the sign of the answer is the same as the sign of the divisor.

In mod(4,-13): 4-(-13)*INT(4/-13) which is...
25. ## Thread: Sort names in tables A-Z

by kweaver
Replies
9
Views
177

### Re: Sort names in tables A-Z

As long as the sort range is still from CV to DB it should be fine.
Results 1 to 25 of 150
Page 1 of 6 1