Um, yes you did. ;)
Type: Posts; User: rory; Keyword(s):
Um, yes you did. ;)
Thankfully I have 2010 now. :)
No, not entirely. Probably about 80-90% compatibility.
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.
Have you tried using the ISNULL on the D.Amount in the Subquery?
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)))
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...
You need to add up the matching results, otherwise you'll just get the first one. You also need to use COLUMN not ROW:
for example, but MATCH with IFERROR...
Its one saving grace was that it made me feel good about having 2007 on my computer at work. :)
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...
If there's only one match, you can use:
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
If only that were true! :)
And don't worry - I've become used to it. At least you didn't call me Rosy...
Shorter formulas will make your workbook smaller, but not necessarily faster.
What's the page? My best guess would be that the first part is a range, not a subtraction.
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:
Quite understandable, RetiredGreek. :)
... just spell my name right... ;)
Ah - I was wondering! :)
Thank you too!
Does it not appear in Name Manager?
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.
No need for the j there:
will reset the whole array.
FWIW the cell format issue is usually restricted to custom number formats - bold, italic etc don't add anything new to the workbook.
You could try this add-in.
As an alternative: