?
Solved

Count Wins/Losses of Last N games Error in formula

Posted on 2014-09-25
15
Medium Priority
?
125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +1
15 Comments
 

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 23

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 2000 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 23

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 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