Solved

Count Wins/Losses of Last N games Error in formula

Posted on 2014-09-25
15
115 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

839 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