 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Problems referencing dates with the MATCH formula
I am finding this a little unpredictable. Sometimes it works fine; for example: A B C D Date Mar-08 Feb-08 Jan-08 If I wish to return the column number from the dates: Mar-08 =MATCH(A2,$A$1:$D$1) returns the value 2 Feb-08 =MATCH(A3,$A$1:$D$1) returns the value 3 Jan-08 =MATCH(A4,$A$1:$D$1) returns the value 4 However if I expand the reference to include column E, Dec-07 I get this result: Mar-08 =MATCH(A2,$A$1:$E$1) returns the value 5 (which is the total number of cells in the reference). Can anyone explain why this would be the case? (The dates are actually 01/03/2008 (UK style) but I have used custom format to display them as mmm-yy. Also the dates are always the first of the month.) Then next part of my question is a little more complex. In column A I have a product names In column B I have the first date that this product was sold. In Column D to A I have the product sales by month. I would like to use the AVERAGE formula in in column C to calculate the average sales over time. However not all products have the same history; some are more recent than others; products have the value zero in the months before they appeared. Therefore if a product has been around since the start the formula will be =AVERAGE(D2:Z2) But if the product has only been around for the past three months the formula will be =AVERAGE(D2:F2) I would like to use the MATCH formula (or a similar formula) to replace the second cell reference in my AVERAGE formula. That is, a nested formula that references the values in column B (the date the product was first sold) and returns the appropriate column reference. I hope I have been clear. Can anyone help with either of these question? I would be eternally grateful. Harry Hi Harry, Try using =MATCH(A2,$A$1:$E$1,0) From help; If match_type is omitted, it is assumed to be 1. If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order. Ed Ferrero www.edferrero.com > Hi Harry, > [quoted text clipped - 11 lines] > > Ed Ferrerowww.edferrero.com Thank you very much for answering the first part of my question. Yes that did the trick. Try setting the third parameter of the MATCH function to zero, to ensure that you are looking for an exact match, i.e.: =MATCH(A2,$A$1:$E$1,0) In answer to your second question, you can use this array* formula: =AVERAGE(IF(D2:Z2<>0,D2:Z2)) which will only count non-zero entries in the average. * An array formula has to be committed using the key combination of CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. Another way of avoiding counting the zeros in the average is to use COUNTIF: =SUM(D2:Z2)/COUNTIF(D2:Z2,">0") or if you really want to use a MATCH function to adjust the range of the AVERAGE function, then you will have to do so within an INDIRECT function. Hope this helps. Pete > I am finding this a little unpredictable. Sometimes it works fine; for > example: [quoted text clipped - 40 lines] > > Harry > Try setting the third parameter of the MATCH function to zero, to > ensure that you are looking for an exact match, i.e.: [quoted text clipped - 71 lines] > > - Show quoted text - Thank for your advice which I am sure I will have call to use some time. In this case I would like to include the zeros. The only zero's I do not want to include are the zeros that are there because the product had not yet been released. I had a feeling that I would need to use the INDIRECT formula in some way, but I am not sure how to use it. I'll try reading some more, thank you. =AVERAGE(D2:INDIRECT(.... Will it look something like the above? It will look something like: =AVERAGE(INDIRECT("D2:" & your_match_function_turning_col_number_to_letter & "2")) As you are only interested in up to column Z, you can use the CHAR function to convert the match number to a letter. Hope this helps. Pete > > Try setting the third parameter of the MATCH function to zero, to > > ensure that you are looking for an exact match, i.e.: [quoted text clipped - 86 lines] > > - Show quoted text - > It will look something like: > [quoted text clipped - 100 lines] > > - Show quoted text - Thank you very much that is a big help. But in fact I actually go up to column BD. My example was simplified. Can I still use the MATCH function in someway here? I'm a bit confused about the column references you are using. In your first example you just mentioned about columns A to E, then you mentioned a range from D to Z, and now you talk about going up to BD - that's why I didn't actually give you a formula to do this. If you can spell out exactly how your data is laid out then I might be able to quote a formula to do it. Pete > Thank you very much that is a big help. But in fact I actually go up > to column BD. My example was simplified. > Can I still use the MATCH function in someway here > I'm a bit confused about the column references you are using. In your > first example you just mentioned about columns A to E, then you [quoted text clipped - 10 lines] > > - Show quoted text - I am sorry Pete. You have actually been a big help and you have helped improve my understanding a lot. I tried to keep my example simple. Also I made a typo in my first post. "In Column D to A I have the product sales by month." It should not have ben D to A. I meant "Column D to Z I have product sales by month". In my real speadsheet I actually have Column D to BD, that is months Mar-08 to Dec-03. Column A contains the names of the products. Column B contains the months the product was first sold (format mmm- yy) Column C contains the average monthly sales from the time that the specific product was first sold. The first date of sale varies from product to product. Column D to BD is the months (format mmm-yy) Put simply I am calculating the average sales of each product. But I need something a little more sophisticated than just =AVERAGE(D:BD) because not all products went on sale for the first time at the same time. The array formula you provided would be okay except that in some months a product may sell zero. I still want to include that zero in my average. Okay, with that layout this formula will return the column ID as a letter (or letters): =IF(MATCH(B2,D$1:BD$1,0)+3>26,CHAR(INT((MATCH(B2,D$1:BD$1,0)+2)/ 26)+64),"")&CHAR(MOD(MATCH(B2,D$1:BD$1,0)+2,26)+65) (I'm sure there must be a better way, but this works for now!!) So, your average formula will be (in C2): =AVERAGE(INDIRECT("D"&ROW(C2)&":"&IF(MATCH(B2,D$1:BD $1,0)+3>26,CHAR(INT((MATCH(B2,D$1:BD$1,0)+2)/ 26)+64),"")&CHAR(MOD(MATCH(B2,D$1:BD$1,0)+2,26)+65)&ROW(C2))) I've had to use the ROW function in order for the row reference to increment as you copy the formula down - it doesn't matter what reference is actually in there, though I've used C2. So, copy this down and it should give you what you want - obviously, if you have no values in columns D to BD then you will get #DIV/0 error, and if you have no date in column B you will get the #N/A error, so you might want to trap these before copying down. Hope this helps. Pete > I am sorry Pete. You have actually been a big help and you have helped > improve my understanding a lot. [quoted text clipped - 21 lines] > months a product may sell zero. I still want to include that zero in > my average > Okay, with that layout this formula will return the column ID as a > letter (or letters): [quoted text clipped - 22 lines] > > Pete You're a brilliant man Pete! Works like a charm. Thanks for showing me the first part of the formula too, so I could see how it works. You have something I can study to so that I can understand it properly. Cheers You're a brilliant man Pete! Works like a charm. Thanks for showing me the first part of the formula too, so I could see how it works. You have given me something I can study to so that I can understand it properly. Cheers Well, thanks for feeding back - I'm glad it did the job for you. The first part of the formula was the complex bit, so that's why I gave you that separately - glad you appreciate it, as we are all here to learn. Pete > You're a brilliant man Pete! Works like a charm. > Thanks for showing me the first part of the formula too, so I could [quoted text clipped - 3 lines] > properly. > Cheers |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.