Search:

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

Page 1 of 20 1 2 3 4

Search: Search took 0.11 seconds.

  1. Thread: find col and row

    by rory
    Replies
    18
    Views
    553

    Um, yes you did. ;)

    Um, yes you did. ;)
  2. Thread: which is faster?

    by rory
    Replies
    19
    Views
    344

    Thankfully I have 2010 now. :)

    Thankfully I have 2010 now. :)
  3. Thread: which is faster?

    by rory
    Replies
    19
    Views
    344

    No, not entirely. Probably about 80-90%...

    No, not entirely. Probably about 80-90% compatibility.
  4. Thread: which is faster?

    by rory
    Replies
    19
    Views
    344

    Sorry typo - yes it should be H2, not H" ...

    Sorry typo - yes it should be H2, not H"

    COUNTIFS should be faster than SUMPRODUCT generally. You could also use another helper column that concatenates columns E and G and then just use COUNTIF.
  5. Replies
    2
    Views
    109

    Have you tried using the ISNULL on the D.Amount...

    Have you tried using the ISNULL on the D.Amount in the Subquery?
  6. Thread: which is faster?

    by rory
    Replies
    19
    Views
    344

    IIRC, Excel 2008 can use COUNTIFS so that should...

    IIRC, Excel 2008 can use COUNTIFS so that should be better:

    =IF(LEN($E3)<2, H2, IF(G3<>"Y", H2, H"+(COUNTIFS($E$1:$E2,$E3,G$1:G2,"Y") > 0)))
  7. Thread: find col and row

    by rory
    Replies
    18
    Views
    553

    But your formula (= (B9:J9=A1)*ROW(1:9)) is only...

    But your formula (= (B9:J9=A1)*ROW(1:9)) is only looking at row 9, so the answer is self evident, no? ;)



    SUMPRODUCT (which isn't actually in the formula you mentioned) ignores booleans, so you...
  8. Thread: find col and row

    by rory
    Replies
    18
    Views
    553

    You need to add up the matching results,...

    You need to add up the matching results, otherwise you'll just get the first one. You also need to use COLUMN not ROW:
    =SUMPRODUCT((B9:J9=$A$1)*(COLUMN(B9:J9)))
    for example, but MATCH with IFERROR...
  9. Thread: find col and row

    by rory
    Replies
    18
    Views
    553

    Its one saving grace was that it made me feel...

    Its one saving grace was that it made me feel good about having 2007 on my computer at work. :)
  10. [SOLVED] Calculated fields in a pivot table always SUM -...

    Calculated fields in a pivot table always SUM - you can't use a count. If you have PowerPivot, it's a pretty simple measure, but if not, you'll have to add a column to the source data that returns 1...
  11. Thread: find col and row

    by rory
    Replies
    18
    Views
    553

    If there's only one match, you can use:...

    If there's only one match, you can use:
    =IF(COUNTIF($B$1:$H$10,A1)>0,SUMPRODUCT(($B$1:$H$10=A1)*ROW($B$1:$H$10)),"")
    and...
  12. Replies
    9
    Views
    259

    [SOLVED] Those are the returns for a $1 stake (i.e. they...

    Those are the returns for a $1 stake (i.e. they include the original stake), so in terms of profit (or odds) change, it's actually (0.75-0.55)/0.75
  13. Thread: a shorter formula

    by rory
    Replies
    16
    Views
    634

    If only that were true! :) And don't worry -...

    If only that were true! :)

    And don't worry - I've become used to it. At least you didn't call me Rosy...
  14. Thread: a shorter formula

    by rory
    Replies
    16
    Views
    634

    Shorter formulas will make your workbook smaller,...

    Shorter formulas will make your workbook smaller, but not necessarily faster.
  15. Replies
    9
    Views
    259

    [SOLVED] What's the page? My best guess would be that the...

    What's the page? My best guess would be that the first part is a range, not a subtraction.
  16. Thread: a shorter formula

    by rory
    Replies
    16
    Views
    634

    I thought it was implicit in my post that my...

    I thought it was implicit in my post that my formula was actually a named formula called 'a' so the cell formula is actually just:
    =a
    :)
  17. Thread: Lost Named Range?

    by rory
    Replies
    10
    Views
    310

    Quite understandable, RetiredGreek. :)

    Quite understandable, RetiredGreek. :)
  18. Thread: Lost Named Range?

    by rory
    Replies
    10
    Views
    310

    ... just spell my name right... ;)

    ... just spell my name right... ;)
  19. Thread: Lost Named Range?

    by rory
    Replies
    10
    Views
    310

    Hi zeddy Ah - I was wondering! :) Thank you...

    Hi zeddy

    Ah - I was wondering! :)

    Thank you too!
  20. Thread: Lost Named Range?

    by rory
    Replies
    10
    Views
    310

    Does it not appear in Name Manager?

    Does it not appear in Name Manager?
  21. Replies
    7
    Views
    370

    Sadly, you are wrong. The security implementation...

    Sadly, you are wrong. The security implementation was changed in 2013 and now unprotecting sheets in code takes MUCH longer than it used to, which is a real pain for a lot of people.
  22. Replies
    5
    Views
    200

    No need for the j there: Erase RangeArray2...

    No need for the j there:

    Erase RangeArray2
    will reset the whole array.
  23. Replies
    9
    Views
    870

    FWIW the cell format issue is usually restricted...

    FWIW the cell format issue is usually restricted to custom number formats - bold, italic etc don't add anything new to the workbook.
  24. Replies
    7
    Views
    370

    You could try this add-in.

    You could try this add-in.
  25. Thread: a shorter formula

    by rory
    Replies
    16
    Views
    634

    As an alternative:...

    As an alternative:
    =COUNT(INDEX($L$16:$O$47,0,nbr_tests_sem))
Results 1 to 25 of 500
Page 1 of 20 1 2 3 4