Search:

Type: Posts; User: XOR LX; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.11 seconds.

  1. Replies
    6
    Views
    168

    Re: Standard deviation of correlation matrix

    Or, array formula**:

    =STDEV(IF(COLUMN(A1:E5)>ROW(A1:E5),A1:E5))


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down...
  2. Re: converting a 6 digit string into a 3 character string(HELP)

    Alternative, for Office 365, array formula**:

    =CONCAT(CHAR(MID(A1,{2,4,6},1)+IF(MID(A1,{1,3,5},1)="3",48,64)))

    Regards


    **Array formulas are not entered in the same way as 'standard'...
  3. Re: Averageif across multiple sheets with variable content

    Ok, glad we got it sorted!

    And you're welcome!

    Cheers
  4. Re: Averageif across multiple sheets with variable content

    This makes me suspect you are using a US version of Excel, correct? If so, the dates in the formula I gave you need rewriting so that they are of the form mm/dd/yyyy, not dd/mm/yyyy.

    Regards
  5. Re: Averageif across multiple sheets with variable content

    Try the following array formula**:

    =SUM(IFERROR(SUMIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA",INDIRECT("'Week...
  6. Re: Averageif across multiple sheets with variable content

    Apologies. Posted without seeing your previous. Thanks. Will get back to you with a solution soon.
  7. Re: Averageif across multiple sheets with variable content

    Happy to help, though the intention of my previous post wasn't just to debate the correct method of averaging. The relevant point is that there's a big difference in a formula which calculates over a...
  8. Re: Averageif across multiple sheets with variable content

    Hi,

    Are you sure that you should be generating your multi-sheet average via taking an 'average of averages' (a dubious and risky measure, in general)?

    This will only give correct results if the...
  9. Re: Count the character number behind a certain character

    Not sure what you mean. It returns 14 for the example you give, as requested.

    Regards
  10. Re: Count the character number behind a certain character

    Hi,

    Assuming 9 is a suitable upper bound on the number of slashes in the string:

    =-LOOKUP(1,-FIND("ζ",SUBSTITUTE(A1,"","ζ",{1;2;3;4;5;6;7;8;9})))-1

    Regards
  11. Re: How to extract multiple words in meddle of the cell

    Hi,

    What do you mean by "between tags"? I see no "tags" in that sentence.

    If there are no physical delimiters present around the desired extractions, can you clarify the logic that determines...
  12. Re: Average of Count of Positive Values in a Row - with Gaps

    Wait, I think we can use something even simpler (and no CSE).

    Assuming X2 is unoccupied:

    =COUNTIF(A2:W2,">0")/SUM(COUNTIFS(A2:W2,">0",B2:X2,{0,""}))

    Regards
  13. Re: Average of Count of Positive Values in a Row - with Gaps

    Alternative, also with CSE:

    =AVERAGE(IFERROR(1/(1/(FREQUENCY(IF(A2:W2,COLUMN(A2:W2)),IF(A2:W2=0,COLUMN(A2:W2))))),""))

    Regards
  14. Re: Formula to count the number of 0s in between two 1s as you go down the rows for a column

    So what's your expected result for the example you posted?

    And why isn't the 0 in B3 considered for the count? Or the three zeroes in B17:B19? Etc., etc.

    Regards
  15. Re: More convenient way to return text with Boolean?

    Hi,

    Can you give a small practical example with expected result?

    Regards
  16. Re: compare text in two columns and return the difference in a third

    Hi,

    What version of Excel is this for?

    Also, why is MIA the result for:

    7MIAWTF 7COAWTF

    ?
  17. Thread: Moving Average

    by XOR LX
    Replies
    17
    Views
    514

    Re: Moving Average

    Ignore that. Doesn't work.
  18. Thread: Moving Average

    by XOR LX
    Replies
    17
    Views
    514

    Re: Moving Average

    How about (with CSE):

    =AVERAGE(INDEX(B4:K4,N(IF(1,MATCH(1,0/B4:K4)-{0,1,2,3,4}))))

    Regards
  19. Replies
    10
    Views
    554

    Re: SUMIFS Multiple Criteria - Same Column

    @Marcelo

    The only thing you have to be careful about with such constructions, in which the two Named Ranges (A and J) are of different dimensions, is that any #N/A errors within the range will...
  20. Re: COUNTIF question - counting cells which contain a specific letter...

    If your assumption is correct then we can also use:

    =COUNTIF(A2:A9,"?*")

    though note that your count will also include any null strings ("") within the range, should there be any.

    Regards
  21. Re: Extract only unique 1X2 and result must show in order 1X2

    You're welcome!

    Cheers
  22. Re: Extract only unique 1X2 and result must show in order 1X2

    Ciao, Marcelo.

    OP's using Excel 2000! :eeek:

    Also, that doesn't need CSE.

    Cheers
  23. Re: Extract only unique 1X2 and result must show in order 1X2

    Hi,

    In C13:

    =IF(ROWS(C$6:C6)>COUNT(MATCH({1,"X",2},C$6:C$8,0)),"",INDEX({1,"X",2},SMALL(IF(ISNUMBER(MATCH({1,"X",2},C$6:C$8,0)),{1,2,3}),ROWS(C$6:C6))))

    Copy down and across as required.
    ...
  24. Replies
    8
    Views
    375

    Re: adding numbers based on two columns

    And you followed the instructions at the foot of my original post on how to enter an array formula correctly?

    Regards
  25. Replies
    8
    Views
    375

    Re: adding numbers based on two columns

    That's not a formula to be entered into the worksheet; it is a defined name, to be entered in Name Manager, as described in my original post, which I suggest you re-read more carefully.

    Regards
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4