Search:

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

Page 1 of 6 1

1. Thread: Standard deviation of correlation matrix

by XOR LX
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. Thread: converting a 6 digit string into a 3 character string

by XOR LX
Replies
4
Views
182

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. Thread: Averageif across multiple sheets with variable content

by XOR LX
Replies
11
Views
331

Re: Averageif across multiple sheets with variable content

Ok, glad we got it sorted!

And you're welcome!

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

by XOR LX
Replies
11
Views
331

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. Thread: Averageif across multiple sheets with variable content

by XOR LX
Replies
11
Views
331

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. Thread: Averageif across multiple sheets with variable content

by XOR LX
Replies
11
Views
331

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. Thread: Averageif across multiple sheets with variable content

by XOR LX
Replies
11
Views
331

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. Thread: Averageif across multiple sheets with variable content

by XOR LX
Replies
11
Views
331

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. Thread: Count the character number behind a certain character

by XOR LX
Replies
17
Views
316

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. Thread: Count the character number behind a certain character

by XOR LX
Replies
17
Views
316

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. Thread: How to extract multiple words in middle of the cell

by XOR LX
Replies
4
Views
342

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. Thread: Average of Count of Positive Values in a Row - with Gaps

by XOR LX
Replies
7
Views
206

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. Thread: Average of Count of Positive Values in a Row - with Gaps

by XOR LX
Replies
7
Views
206

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. Thread: Formula to count the number of 0s in between two 1s as you go down the rows for a column

by XOR LX
Replies
10
Views
378

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. Thread: More convenient way to return text with Boolean?

by XOR LX
Replies
5
Views
290

Re: More convenient way to return text with Boolean?

Hi,

Can you give a small practical example with expected result?

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

by XOR LX
Replies
4
Views
214

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

?

by XOR LX
Replies
17
Views
514

Re: Moving Average

Ignore that. Doesn't work.

by XOR LX
Replies
17
Views
514

Re: Moving Average

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

Regards
19. Thread: SUMIFS Multiple Criteria - Same Column

by XOR LX
Replies
10
Views
553

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. Thread: COUNTIF question - counting cells which contain a specific letter...

by XOR LX
Replies
37
Views
5,810

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. Thread: Extract only unique 1X2 and result must show in order 1X2

by XOR LX
Replies
8
Views
403

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

You're welcome!

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

by XOR LX
Replies
8
Views
403

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. Thread: Extract only unique 1X2 and result must show in order 1X2

by XOR LX
Replies
8
Views
403

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.
...

by XOR LX
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

by XOR LX
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