Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Count Wins/Losses of Last N games Error in formula

Posted on 2014-09-25
15
Medium Priority
?
134 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: 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
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.

 

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 24

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 24

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

Technology Partners: 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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

581 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