I'm trying to do a calculation in cell F4, where I refer to cell B5. However, the result becomes #N/A. Then, when I write "15-04", which is the value that cell B5 contains, it does work. Does any of you know what the reason could be for Excel displaying #N/A when I refer to the cell (I would like it to refer to the cell as the value is a calculation as well and thus will change in time).
Best regards
Kasper
B5 contains the following formula:
Formula:
Please Login or Register to view this content.
F4 contains the following formula:
Formula:Please Login or Register to view this content.
=AVERAGE(INDEX('Total Error %'!B:B,MATCH(B5,'Total Error %'!A:A,0)+1):INDEX('Total Error %'!B:B,MATCH(B5,'Total Error %'!A:A,0)))
What's this colon doing here just before the second INDEX?
Shouldn't that be a comma?
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
When I try to remove the colon, I get the following error message (see picture below)
Excel suggests that I use an asterisk instead but I still get #N/A as output.
Sounds like your dates are text, looking like dates?
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I have attached a sample doc. The outcome should be somewhere between 150 and 250 for fixtures and between 40 and 80 for errors.
As I said, your dates are not real dates, they are text.
I will see what I can come up with for you
Thanks. In F4 and G4, I accidentally wrote B5 twice instead of B2 and B5 (I've bolded the changes). Here are the correct formulae:
F4:
Formula:Please Login or Register to view this content.
G4:
Formula:Please Login or Register to view this content.
But the cells they are referring forward to are also customized as "YY/MM", so it should not be a problem, should it?
Try this...
B3=LEFT(B2,4)&RIGHT(B2,2)-1
B5=LEFT(B2,2)-1&RIGHT(B2,3)
It would be FAR simpler if you used actual (real) dates....dd/mm/yy or mm/dd/yy, depending on your how you display dates
Your work-around works but once I change B2 to another month B3 and B5 do not follow accordingly. I know that it would be easier if I used real dates but I've got a huge document where everything is written as YY/MM. However, I think I may want to try and change it all, preferably using a search function (it has just proven to be somewhat difficult for me earlier). I'll give it another go. Thanks a lot mate.
Hello
As your source data is a Table there's no need to reference entire columns. For example this returns 236 for Fixtures this month without any change in format:
Formula:Please Login or Register to view this content.
is that correct?
DBY
DBY: In a month I'll add new data (fixture count), which will then be the new B2 value in the table. But I'd like to have the freedom to manually write in the month I'd like to analyze and then have Excel calculate the specifics of that month, the previous month, the same month a year ago as well as the average of the past twelve months. Thus I would want to look in the column for the month corresponding to the one selected as "month chosen to analyze"
1 thing to remember is that if you are working with dates, use REAL dates, excel has ways to deal with real dates, but your data is just text.
for the F/R, try this approach
1. Find -01 Replace -01-01
2. repeat with -02 etc (make sure you start with -01 and work upwards, you only need to do that 12 times)
3. Convert to a real date using a helper column and this, copied down...
=DATE(2000+LEFT(J2,2),MID(J2,4,2),1)
I used column J for my testing, but adjust that as needed for your data.
It would probably be a good idea if you saved your work before you did this, or saved as a test file
I don't quite follow you. What does F/R mean and what is -01?
Find and Replace
-01 is what you have as your month
hmm actually, you can skip the F/R and just use this in a helper column...
=DATE(LEFT(A2,2)+2000,RIGHT(A2,2),1)
Then, if you want, you can copy/paste values over your original "dates"
Is there no way of making this work just with month and year (i.e. no actual date?) Cause I have a lot of graphs where it would look quite messy with a date added to it as well.
You can format the axis to show your dates exactly as you have them there
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks