Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.73 seconds.

  1. 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. 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. 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. 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. 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. 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. 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))
  8. Replies
    4
    Views
    98

    Re: Formula Help

    How about:
    =MEDIAN(10,A1,100)
  9. 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. 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. 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. 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. 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. 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))

    <b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color:...
  15. 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)
  16. 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. Replies
    2
    Views
    174

    Re: Nested IF x50

    Without much else to go on, I would suggest looking at the VLOOKUP function.
  18. 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. 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. 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. Re: Rounding using ceiling with conditions

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

    =max("1:15"+0,ceiling(b2-a2,"00:15"+0))
  23. 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. 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. 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 2 3 4