Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to AVERAGE IF but only average a set number of values
I have a chart that has a row of data at the top.
One cell, let's say O5 has a value, let's say 5. I need to search the O column for the value (5). When it locates the 1st line with a 5 in column O (let's say O18) I need it to take the value from Column N (N18) and place it in cell N5. I then need to take the first 4 lines with the value of 5 in Column O (the previous O18, and let's say O26, O38, and O57) and average the 4 values in column N (N18, N26, N38, & N57) then place the average value in cell N4. I want to search the column O beginning with line 8 and below but as I said I only want the 1st match and the 1st 4 matches. I can't find a way to do this but as the value in O5 continually changes, I need to automate this process. Any assistance is greatly appreciated. Don |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to AVERAGE IF but only average a set number of values
I then need to take the first 4 lines with the value of 5 in Column O
Will there *always* be 4 instances of 5 in column O? -- Biff Microsoft Excel MVP wrote in message ... I have a chart that has a row of data at the top. One cell, let's say O5 has a value, let's say 5. I need to search the O column for the value (5). When it locates the 1st line with a 5 in column O (let's say O18) I need it to take the value from Column N (N18) and place it in cell N5. I then need to take the first 4 lines with the value of 5 in Column O (the previous O18, and let's say O26, O38, and O57) and average the 4 values in column N (N18, N26, N38, & N57) then place the average value in cell N4. I want to search the column O beginning with line 8 and below but as I said I only want the 1st match and the 1st 4 matches. I can't find a way to do this but as the value in O5 continually changes, I need to automate this process. Any assistance is greatly appreciated. Don |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to AVERAGE IF but only average a set number of values
wrote...
.... When it locates the 1st line with a 5 in column O (let's say O18) I need it to take the value from Column N (N18) and place it in cell N5. Use the following formula in N5. =IF(COUNT(MATCH(O5,O8:O1000,0)),INDEX(N8:N1000,MAT CH(O5,O8:O1000,0)), "no match") I then need to take the first 4 lines with the value of 5 in Column O (the previous O18, and let's say O26, O38, and O57) and average the 4 values in column N (N18, N26, N38, & N57) then place the average value in cell N4. .... Try the following ARRAY formula in N4. =IF(COUNTIF(O8:O1000,O5)=4,AVERAGE(IF((O8:O1000=O 5) *(ROW(N8:N1000)<=SMALL(IF(O8:O1000=O5,ROW(N8:N1000 )),4)),N8:N1000)), "fewer than 4 matches") If you want to average all values if there are fewer than 4, try the following ARRAY formula. =IF(COUNT(N5),AVERAGE(IF((O8:O1000=O5)*(ROW(N8:N10 00) <=SMALL(IF(O8:O1000=O5,ROW(N8:N1000)),MIN(COUNTIF( O8:O1000,O5),4))), N8:N1000)),N5) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average only first number of actual values | Excel Worksheet Functions | |||
How can I average certain number of lowest values in a column? | New Users to Excel | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
How do I average a formula without calculating zero values? | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |