Solved

Count Wins/Losses of Last N games Error in formula

Posted on 2014-09-25
15
108 Views
Last Modified: 2014-09-27
cells AC6:AD21 have the following formula or its equivalent for each cell

=SUM(--(INDIRECT("'2014-2'!"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)))>0)*ROW('2014-2'!$A$5:$A$1000),$AD$4),MATCH($AB6,'2014-2'!$A$3:$CM$3,0))&":"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)))>0)*ROW('2014-2'!$A$5:$A$1000),1),MATCH($AB6,'2014-2'!$A$3:$CM$3,0)))>INDIRECT("'2014-2'!"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1))>0)*ROW('2014-2'!$A$5:$A$1000),$AD$4),MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1)&":"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1))>0)*ROW('2014-2'!$A$5:$A$1000),1),MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1))))

However, today I get the following error,

statserror.jpg
Apparently the file changed somehow to the following...

...Internet Files\Content.IE5\ZJZ9GQ29\[W-L-of-last-N-games.xlsx]2014-2'!$A$3:$CM$3,0)+1))))

I attempted to delete the extra stuff infront of the file name (i.e. '2014-2') but I get another error.

If someone can please tell me how to fix this.
0
Comment
Question by:Pedrov664
  • 7
  • 3
  • 3
  • +1
15 Comments
 

Author Comment

by:Pedrov664
ID: 40344099
0
 

Author Comment

by:Pedrov664
ID: 40344109
P.P.S.

I tried copying the original formula as shown in the solution above and I get a '#NUM!' error in that cell all other cells have a '#REF!' error. I assume the last error is because it is looking for a file that does not exist but in reality it should be looking for sheet named '2014-2'.
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40344142
at first glance it looks like you may have had a named range, and now maybe that named range is wrong or gone?
0
 

Author Comment

by:Pedrov664
ID: 40344152
I do see the difference between the original formula and the 'other' formula which adds

"...Internet Files\Content.IE5\ZJZ9GQ29\[W-L-of-last-N-games.xlsx]" before the file name. Thus, it is looking the sheet in a different file than the one I am using. It should not do that. I do not know how this formula got changed.
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40344179
You may have to work your way backwards and deconstruct the file name that is being concatenated with ADDRESS 2014... , and then referenced with INDIRECT
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40344181
I say filename, but it looks like a cel reference to me.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40344624
Hello Pedrov664,

I didn't try to decipher your formula but in my experience there's always a better approach than using ADDRESS function to return a reference as text and then using INDIRECT to convert to a real reference (which is what's happening in your formula). That's a very inefficient way to approach the problem.

If you could explain in a little more detail how your data is laid out and what results you are trying to get (or post a sample of your data) I think a more streamlined formula is possible, if you're interested

regards, barry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Pedrov664
ID: 40344832
Barry,

I have attached a file that contains the data sheets.

The problem is in the 'Current Game 2' sheet. I have used green background to highlight the cells that contain the errors.

Hope you can help.

Pedro
EE-gamestats-2014.xlsm
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40344932
The link problem is because you copied/moved some of the formulas from another workbook, and the link is to that workbook.

The formula is an array formula, so it has to be entered with Ctrl+Shift+Enter.
In the file it is not an array formula, so it returns an error.

Here is the file with the formulas updated, and no links.

Interested in seeing what Barry can make.
EE-gamestats-2014.xlsm
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 40345013
It looks like Ejgil has fixed your specific problem here but if you are interested in a shorter formula you can use this "array formula" in AC6

=IF($AB6=0,"",SUM((MMULT(OFFSET('2014-2'!$A$4,LARGE(IF(INDEX('2014-2'!$B$5:$CK$1000,0,MATCH($AB6,'2014-2'!$B$3:$CK$3,0))<>"",ROW('2014-2'!B$5:B$1000)-ROW('2014-2'!B$5)+1),15),MATCH($AB6,'2014-2'!$B$3:$CK$3,0),1000,2)+0,{1;-1})>0)+0))

confirm with CTRL+SHIFT+ENTER and copy down

You can use exactly the same formula in AD6 copied down except the > at the end should be changed to a <

regards, barry
0
 

Author Comment

by:Pedrov664
ID: 40345051
What about the other side? Will copy and paste work?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40345116
If you copy AC6:AD6 to AJ6:AK6 then all you have to change are all the references to AB6, which become AI6.....then you can copy the formula down there too

see attached

regards, barry
EE-gamestats-2014-bh.xlsm
0
 

Author Comment

by:Pedrov664
ID: 40345948
Egjil,

I did not know about the crtl, shift, enter routine.

Barry,

You solution is easier to keep track of. I will use that one in this case.

Thanks you.

P.S. I will try it for a day or two to make sure I got the hang of it.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40346503
Barry.
I have never used matrix multiplication before, but your solution really shows the benefit.
0
 

Author Closing Comment

by:Pedrov664
ID: 40347557
The Great Houdini has done it again!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now