Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
I am requiring filtering of a list of employees over a who are scored on
performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
In Excel 2003
A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Unfortunately I am in Excel XP (2002). Any solution for that version. "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
I tried the equation and get a "#value" result. Looking at it more closely,
the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Did you remember to enter it as an array formula?
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
I get a #DIV/0! result. Is the averaging complicating the issue? In the
end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Share the formula that you used.
And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
I did some further reading and working on Array formulas and have worked the
problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
I would think that since you're looking for a single value--the average, then
the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Alright, I will eliminate the Date range. Yes there are names that match.
No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
First, you have to have dates larger than or equal to the 5th largest--or
arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
There are dates that are larger or requal to the 5th largest.
There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Select the cell with the array formula:
=if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Here are some of the results from debugging.
On the {=if(myname=a1,"good")}, if the "Good" preceeds "False", "Good" is returned. If a "False" preceeds "Good", "False" is returned. I would think that if all arguments must be met to render a Good or False; not the position of the result. Or is this unique to an array formula? Maybe this is not the correct formula. What do you think? The second formula, {=if(Mydate=Large(Mydate,5),Myscore)}, returned the expected result. The score of the 5th largest date. "Dave Peterson" wrote: Select the cell with the array formula: =if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Correction:
On the second formula, It should be an equal sign not a greater than sign. I am looking for the 5th largest date and score, not anything greater then the 5th largest score. Really, I am looking for the top 5 scores, but let finish this one first. "kwh" wrote: Here are some of the results from debugging. On the {=if(myname=a1,"good")}, if the "Good" preceeds "False", "Good" is returned. If a "False" preceeds "Good", "False" is returned. I would think that if all arguments must be met to render a Good or False; not the position of the result. Or is this unique to an array formula? Maybe this is not the correct formula. What do you think? The second formula, {=if(Mydate=Large(Mydate,5),Myscore)}, returned the expected result. The score of the 5th largest date. "Dave Peterson" wrote: Select the cell with the array formula: =if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
If the formula returns an array, you're only going to see the first item in that
array in the cell. If you wanted to check to see if all the cells in myDate match A1, you could use: =and(myname=a1) The second formula shouldn't have returned a simple value. It should also return an array. Do the same F2, then F9 to see the array in the formula bar. kwh wrote: Here are some of the results from debugging. On the {=if(myname=a1,"good")}, if the "Good" preceeds "False", "Good" is returned. If a "False" preceeds "Good", "False" is returned. I would think that if all arguments must be met to render a Good or False; not the position of the result. Or is this unique to an array formula? Maybe this is not the correct formula. What do you think? The second formula, {=if(Mydate=Large(Mydate,5),Myscore)}, returned the expected result. The score of the 5th largest date. "Dave Peterson" wrote: Select the cell with the array formula: =if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
you were right about the second formula. the test equation of
C1=LARGE(MYDATE,5) returned the correct answer. In essence, this is not the right formula for what I need? "kwh" wrote: Correction: On the second formula, It should be an equal sign not a greater than sign. I am looking for the 5th largest date and score, not anything greater then the 5th largest score. Really, I am looking for the top 5 scores, but let finish this one first. "kwh" wrote: Here are some of the results from debugging. On the {=if(myname=a1,"good")}, if the "Good" preceeds "False", "Good" is returned. If a "False" preceeds "Good", "False" is returned. I would think that if all arguments must be met to render a Good or False; not the position of the result. Or is this unique to an array formula? Maybe this is not the correct formula. What do you think? The second formula, {=if(Mydate=Large(Mydate,5),Myscore)}, returned the expected result. The score of the 5th largest date. "Dave Peterson" wrote: Select the cell with the array formula: =if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Did you try the helper columns and autofilter?
kwh wrote: Correction: On the second formula, It should be an equal sign not a greater than sign. I am looking for the 5th largest date and score, not anything greater then the 5th largest score. Really, I am looking for the top 5 scores, but let finish this one first. "kwh" wrote: Here are some of the results from debugging. On the {=if(myname=a1,"good")}, if the "Good" preceeds "False", "Good" is returned. If a "False" preceeds "Good", "False" is returned. I would think that if all arguments must be met to render a Good or False; not the position of the result. Or is this unique to an array formula? Maybe this is not the correct formula. What do you think? The second formula, {=if(Mydate=Large(Mydate,5),Myscore)}, returned the expected result. The score of the 5th largest date. "Dave Peterson" wrote: Select the cell with the array formula: =if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering by top scores
Thanks Dave for your help. Thought this issue was not resolved and I think
it was my approach, I learned a great deal about array formulas and how to use them. "Dave Peterson" wrote: If the formula returns an array, you're only going to see the first item in that array in the cell. If you wanted to check to see if all the cells in myDate match A1, you could use: =and(myname=a1) The second formula shouldn't have returned a simple value. It should also return an array. Do the same F2, then F9 to see the array in the formula bar. kwh wrote: Here are some of the results from debugging. On the {=if(myname=a1,"good")}, if the "Good" preceeds "False", "Good" is returned. If a "False" preceeds "Good", "False" is returned. I would think that if all arguments must be met to render a Good or False; not the position of the result. Or is this unique to an array formula? Maybe this is not the correct formula. What do you think? The second formula, {=if(Mydate=Large(Mydate,5),Myscore)}, returned the expected result. The score of the 5th largest date. "Dave Peterson" wrote: Select the cell with the array formula: =if(myname=a1,"good") and hit F2, then F9. You should see an array that looks like: ={"good";FALSE;FALSE;FALSE;FALSE;"good";FALSE;FALS E} If I were trying to debug my formula, I'd add a couple of formulas in some helper columns. Formulas that would return simple true/false: =b2=$a$1 (say B2 is the first cell in the myName range) and =c2=Large(myDate,5) (say C2 is the first cell in the myDate range) Then drag down. Then filter those two columns to show True for each. See what pops up. kwh wrote: There are dates that are larger or requal to the 5th largest. There are names that meet the date requirment. However, where are some with less then fives dates. In that case I would like to pull the largest date. I would assume the Max function would be used. I am now 100% sure and I have created a smaller test database. Now, as a result from testing each element of the equation. I test the {if(name rang=A1,"Good")}. The first name on the list came back good. The rest resulted in a false, though the name appear on the list. Any thoughts? Second I tested the {=if(Date Range =Large(Date Range,5),Score Range)} I recevied a return but it was the score for the first entry not the fifth largest. I think I understand each elements, but I am not getting my expected results. I am also drifting away from my initial need which was to list the scores from the five most recent dates per employee. If the employee doesn't have five, a list of what is available. (I do want to understand why what we are working on isn't producing the results-I am sure it is my execution) Thanks for you help. "Dave Peterson" wrote: First, you have to have dates larger than or equal to the 5th largest--or arithmetic breaks! My question should have been do you have any rows that match the name requirement and have dates large enough to qualify. Second, you don't have to eliminate the date range--just rename it to something else. Third, be 100% positive that you don't have errors in those ranges. Fourth, create a new test workbook with small ranges that you can test your formula. kwh wrote: Alright, I will eliminate the Date range. Yes there are names that match. No there are not any date greater than the 5th largest. I am 99% sure the data does not have errors it. I will try each element of the formula by itself to see where the error is comming from. I will let you know how it comes out. "Dave Peterson" wrote: I would think that since you're looking for a single value--the average, then the array formula would be a single cell formula. =average() will ignore text and boolean values. =if(test,"value if true") will return "value if true" if test is true. It'll return False if test is false. ps. Excel is pretty smart, but I wouldn't use a named range of Date. It looks too much like the worksheet function =date(). And excel may not get confused, but I would. Do you have any names that match A1 in that range called Name? Do you have any dates greater than the 5th largest date in the range called Date? And you're sure that all the data in Name, Date, and Score has no errors in it, right? kwh wrote: I did some further reading and working on Array formulas and have worked the problem over. First I created unique item listing on another worksheet based on the name field. I created Name ranges for the date, name, and score. Next, I select a range of 5 colums and rows with the same number of the unique names. I entered the equation as follows: {=Average(if(Name=a1,if(Date=Large(Date,5),Score) ))} (Ctrl-Shift Enter). The result is #N/A. Removing the average function, I receive a FALSE message. This is considered a Multicell Array correct? Do you see error in my logic? "Dave Peterson" wrote: Share the formula that you used. And double check to see if your data matches what you used in your formula. kwh wrote: I get a #DIV/0! result. Is the averaging complicating the issue? In the end, like the auto filter, I would like a list of the five most recent dates of each employees. I could manage the average another way. Any thoughts? "Dave Peterson" wrote: Did you remember to enter it as an array formula? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) kwh wrote: I tried the equation and get a "#value" result. Looking at it more closely, the "IF" statements are not completed. Only the first of the two arugments are stated. I would assume that if the argument is not met you would want some kind of message. What do you think? "Barb Reinhardt" wrote: In Excel 2003 A1:A10 are the names B1:B10 are the dates C1:C10 are the scores =AVERAGE(IF(A1:A10="Barb",IF(B1:B10=LARGE(B1:B10, 5),C1:C10))) Commit with CTRL SHIFT ENTER. -- HTH, Barb Reinhardt "kwh" wrote: I am requiring filtering of a list of employees over a who are scored on performance over a period of time. I would like to filter for the top five most recent dates for each employee and average the scores for that filtered data. Is that possible in advanced filtering? If so, how is it done? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filtering | Excel Discussion (Misc queries) | |||
advanced filtering | Excel Discussion (Misc queries) | |||
Advanced filtering | Excel Worksheet Functions | |||
advanced filtering in XLS | Excel Worksheet Functions | |||
Advanced Filtering | Excel Worksheet Functions |