Pedro
asked on
Count Wins/Losses of Last N games Error in formula
cells AC6:AD21 have the following formula or its equivalent for each cell
=SUM(--(INDIRECT("'2014-2'
However, today I get the following error,
Apparently the file changed somehow to the following...
...Internet Files\Content.IE5\ZJZ9GQ29
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.
=SUM(--(INDIRECT("'2014-2'
However, today I get the following error,
Apparently the file changed somehow to the following...
...Internet Files\Content.IE5\ZJZ9GQ29
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.
ASKER
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'.
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'.
at first glance it looks like you may have had a named range, and now maybe that named range is wrong or gone?
ASKER
I do see the difference between the original formula and the 'other' formula which adds
"...Internet Files\Content.IE5\ZJZ9GQ29
"...Internet Files\Content.IE5\ZJZ9GQ29
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
I say filename, but it looks like a cel reference to me.
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
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
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What about the other side? Will copy and paste work?
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
see attached
regards, barry
EE-gamestats-2014-bh.xlsm
ASKER
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.
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.
Barry.
I have never used matrix multiplication before, but your solution really shows the benefit.
I have never used matrix multiplication before, but your solution really shows the benefit.
ASKER
The Great Houdini has done it again!
ASKER
https://www.experts-exchange.com/questions/28523902/Count-Wins-Losses-of-Last-N-games.html?anchorAnswerId=40340255#a40340255