Solved

Count Wins/Losses of Last N games Error in formula

Posted on 2014-09-25
15
105 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
Comment Utility
0
 

Author Comment

by:Pedrov664
Comment Utility
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:Iammontoya
Comment Utility
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
Comment Utility
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:Iammontoya
Comment Utility
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:Iammontoya
Comment Utility
I say filename, but it looks like a cel reference to me.
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Pedrov664
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What about the other side? Will copy and paste work?
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
Comment Utility
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
Comment Utility
Barry.
I have never used matrix multiplication before, but your solution really shows the benefit.
0
 

Author Closing Comment

by:Pedrov664
Comment Utility
The Great Houdini has done it again!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 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

16 Experts available now in Live!

Get 1:1 Help Now