Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.01 seconds.

  1. 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. Replies
    4
    Views
    56

    Re: Formula for 4 for 3

    =sum(a1:a4)-min(a1:a4)
  3. 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. 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. 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. 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. 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. Replies
    8
    Views
    102

    Re: Count # of Sequences in a range

    Is the average for Unit 2 = 1.2333?
  9. 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. Re: SUM ignoring text fields in a non-string of cells

    And, why doesn't =SUM(B:B) work?
  11. 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:


    ="AD-"&TEXT(1+RIGHT(A3,LEN(A3)-FIND("-",A3)),"00")

    or
  12. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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 2 3 4