 |
 | 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 / Worksheet Functions / July 2007Array Formula and 2 Tabs!
I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and the other called "QA Data Sheet". I'm trying to capture all of my relevant data on the QA Data tab, and so far so good. I have a variety of information there. However, when I attempted to capture the 2 following bits of Data (and tested my formula) I could not make it work. The formula seems to work well with words (e.g. "Refusal"), but not so well with numerical values. Any suggestions? Below are the 2 "Array" formulas I'm trying to make work with respect to reporting the frequency (Row H) of BAC levels 0.16% or greater: =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules Sample'!H4:H3500)))) =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500)))) Btw, Roger has been of incredible help to me, but I didn't want to tax him further, so I'm putting this problem out to the group, lol! It's actually simpler if the criteria involves numbers .. For > =SUMPRODUCT((YEAR('New Rules > Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules > Sample'!H4:H3500)))) Put it as: =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules Sample'!H4:H3500>=0.16)) and for: > =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan > 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500)))) this should suffice: =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New Rules Sample'!H4:H3500>=0.16))  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and > the other called "QA Data Sheet". I'm trying to capture all of my relevant [quoted text clipped - 14 lines] > Btw, Roger has been of incredible help to me, but I didn't want to tax him > further, so I'm putting this problem out to the group, lol! Hi Max! I copied and pasted both of your suggestions and got a: #REF! error result :(. HELP! > It's actually simpler if the criteria involves numbers .. > [quoted text clipped - 32 lines] > > Btw, Roger has been of incredible help to me, but I didn't want to tax him > > further, so I'm putting this problem out to the group, lol! That's due to a line break issue when you copy n paste from posts. In the formula bar, just place your cursor on the 2nd line (that's the obvious line break), then remove it by pressing Backspace, then ENTER. All should be ok. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi Max! > > I copied and pasted both of your suggestions and got a: #REF! error result > :(. HELP! Actually Max, after I sent the second note I realized why I got that error (I can be thick sometime). I did get the formula to take, however when I went to test it, I am getting a numerical result on my "QA Data Sheet" tab (where I'm keeping the data) independent of the BAC results. For example if I place a date in Row T that is within the date parameters of 2007 (e.g. Jan 1, 2007, March 4, 2007), I get a result of 2. My thought with this array formula is that I should get a result of 2 if the result in Row H is .16 or greater, AND the result in Row T is of the date paramter the formula is describing (e.g. the month or year). The formula only seems to be tallying its outcome numbers based upon Row T. I hope that made sense? I tested it a few times before writing again to ask for help. I do like the sense of accomplishment from figuring it out, but this array formula just doesn't want to play :( Thanks Max! > It's actually simpler if the criteria involves numbers .. > [quoted text clipped - 32 lines] > > Btw, Roger has been of incredible help to me, but I didn't want to tax him > > further, so I'm putting this problem out to the group, lol! Yes, the formula should work as you described. But .. think I missed this line in your orig. post: > .. the frequency (Row H) of BAC levels 0.16% or greater (I was looking more at your 2 formulated attempts) As 0.16% = 0.0016, you just need correct the part: >=0.16 to read as: >=0.0016 within both formulas. Both should return the correct values. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Actually Max, after I sent the second note I realized why I got that error (I > can be thick sometime). [quoted text clipped - 15 lines] > > Thanks Max! Max you and Roger have been so very helpful tonight. I am appreciative. I still can't seem to make the formula work when I manipulate the data in the 2 applicable Rows (H and T). I sent you the file on email if you have an opportunity or willingness to look at it. If not I'll understand. Best, Dan > I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and > the other called "QA Data Sheet". I'm trying to capture all of my relevant [quoted text clipped - 14 lines] > Btw, Roger has been of incredible help to me, but I didn't want to tax him > further, so I'm putting this problem out to the group, lol! The prob was that there was a mixture of numbers and text within: 'New Rules Sample'!H4:H3500. This can be treated via "adding" another condition into the formula to ensure that only numbers would be involved (ignore text), ie: ISNUMBER('New Rules Sample'!H4:H3500) In QA Data Sheet, Put instead in B11: =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500))) Put instead in B12: =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500))) Note that I've also changed in the above, back to using: >=0.16 from what I see in your descriptions in A11 & A12 One last comment. I noticed you had array-entered all your SUMPRODUCTs. SUMPRODUCT doesn't require array-entering (CSE) unless TRANSPOSE is used within. Just normal ENTER will do. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Max you and Roger have been so very helpful tonight. I am appreciative. I > still can't seem to make the formula work when I manipulate the data in the 2 [quoted text clipped - 4 lines] > > Dan Max I think Excel hates me, lol! I entered the data EXACTLY as you suggested (into the proper cells and not as an Array), and when I went to taste the variables in Rows T and H it wouldn't work. It's odd, because the other variables you saw on my "QA Data Sheet" tab (the text) worked perfectly with the formulas I was given thanks to Roger. These numerical references however just don't want to cooperate. I felt stupid posting again after all the time and effort you put into this for me................... Dan PS: Thank you very much for your time! > The prob was that there was a mixture of numbers and text within: 'New Rules > Sample'!H4:H3500. This can be treated via "adding" another condition into the [quoted text clipped - 25 lines] > > > > Dan Hi Dan The problem is that you have both text and numeric in Column H, where the word Refusal appears. Use the following formulae =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)* ('New Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500))) =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"yyy mm")="Jan 07")* ('New Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500))) SignatureRegards Roger Govier > Max I think Excel hates me, lol! I entered the data EXACTLY as you > suggested [quoted text clipped - 53 lines] >> > >> > Dan Roger, Believe our findings and suggestions to Dan are identical <g> SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > .. I entered the data EXACTLY as you suggested > (into the proper cells and not as an Array), and when I went to taste the > variables in Rows T and H it wouldn't work. Since you have difficulty getting it to work over there, I've sent over the file with the formulas implemented in B11 and B12 for your reference. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Max and Roger are AWESOME! I feel so silly because I copied and pasted the formula into my spreadsheet EXACTLY as you gave it to me (I swear), and I couldn't get it to work properly. The one you sent me however WORKS like a dream. I don't get it, but I am VERY thankful to you and Roger for your help On a positive note, I was however successful last night in doing something similar with the Rows which contain text (e.g. Row G-name of court), but wasn't sure how to do this with Rows containing dates (e.g. Row T-date of Intake Interview). Is the formula for tracking dates off of my "New Rules" tab, and placing it onto my "QA Data Tab" as easy? I'm almost afraid to ask? My intention with some of the date rows is to track total clients (based upon their Intake Dates-Row T, and their Exit Interview Dates-Row Z) by the applicable month as well as the annual total. =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007) -and- =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07") Unfortunately the above formulas returned a "0" result upon testing of the data in Row T (when the applicable dates were placed there) so I must be missing something (I do try on my own to figure it out before reaching out for help, lol). Again, thank you so VERY much to Max and Roger for your help! Respectfully, Dan > > .. I entered the data EXACTLY as you suggested > > (into the proper cells and not as an Array), and when I went to taste the > > variables in Rows T and H it wouldn't work. > > Since you have difficulty getting it to work over there, I've sent over the > file with the formulas implemented in B11 and B12 for your reference. try: =SUMPRODUCT(--(TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")) > Max and Roger are AWESOME! I feel so silly because I copied and pasted the > formula into my spreadsheet EXACTLY as you gave it to me (I swear), and I [quoted text clipped - 33 lines] > > Since you have difficulty getting it to work over there, I've sent over the > > file with the formulas implemented in B11 and B12 for your reference. Thanks Toppers. I was also able to extrapolate appropriately from your formula to change the parameters when searching by year (e.g. 2007). For some reason nothing was working right yesterday with respect to Excel and formulas. Today, everything is going well. Whew! Knock on wood! Dan > try: > [quoted text clipped - 37 lines] > > > Since you have difficulty getting it to work over there, I've sent over the > > > file with the formulas implemented in B11 and B12 for your reference. |
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.