Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
Hi,
try =AVERAGEIF(E6:E9,"Y",D6:D9) change the range to fit your needs if this helps please click yes, thanks "Kgov" wrote: I'm trying to calculate an average based on some criteria in the adjacent columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
If you mean your data as below; then try the below formula. Please note that
this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col A Col B Col C Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5)) N 3 Y 6 N 5 If this post helps click Yes --------------- Jacob Skaria "Kgov" wrote: I'm trying to calculate an average based on some criteria in the adjacent columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
I should have mentioned. I don't have averageif. (not sure what
version...new job) "Jacob Skaria" wrote: If you mean your data as below; then try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col A Col B Col C Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5)) N 3 Y 6 N 5 If this post helps click Yes --------------- Jacob Skaria "Kgov" wrote: I'm trying to calculate an average based on some criteria in the adjacent columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
never mind I see you didn't use averageif.
Is there away to have the formula average 2, 3 or 4 cells depending on the number of years the user selects? It would always start from the last three years so D,C,B. A B C D E F G 10 12 8 15 Y 3 11.6 Avg of 3 years "Jacob Skaria" wrote: If you mean your data as below; then try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col A Col B Col C Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5)) N 3 Y 6 N 5 If this post helps click Yes --------------- Jacob Skaria "Kgov" wrote: I'm trying to calculate an average based on some criteria in the adjacent columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
Average is just Sum divided by count, so use:
=sumif(a1:a5,"Y",b1:b5)/countif(a1:a5,"Y",b1:b5) Regards, Fred. "Kgov" wrote in message ... I should have mentioned. I don't have averageif. (not sure what version...new job) "Jacob Skaria" wrote: If you mean your data as below; then try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col A Col B Col C Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5)) N 3 Y 6 N 5 If this post helps click Yes --------------- Jacob Skaria "Kgov" wrote: I'm trying to calculate an average based on some criteria in the adjacent columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Averages based on criteria
With your sample data in row1 try the below formula in cell G1
=IF(E1="Y",AVERAGE(INDIRECT(ADDRESS(ROW(A1),COLUMN (D1)-F1+1) &":D1")),"") If this post helps click Yes --------------- Jacob Skaria "Kgov" wrote: never mind I see you didn't use averageif. Is there away to have the formula average 2, 3 or 4 cells depending on the number of years the user selects? It would always start from the last three years so D,C,B. A B C D E F G 10 12 8 15 Y 3 11.6 Avg of 3 years "Jacob Skaria" wrote: If you mean your data as below; then try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col A Col B Col C Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5)) N 3 Y 6 N 5 If this post helps click Yes --------------- Jacob Skaria "Kgov" wrote: I'm trying to calculate an average based on some criteria in the adjacent columns. The user has to first identiy Y or N and then determine if the average should be calculated based on 2, 3 or 4 years. Is there a formula that would only averge the correct number of cells 2, 3 or 4 years depending on what the user picked and divide by the right #? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating a Percent based on Tiered criteria | Excel Discussion (Misc queries) | |||
Calculating the mode of a criteria-based range | Excel Discussion (Misc queries) | |||
Calculating Mode for multiple arrays based on criteria in another | Excel Worksheet Functions | |||
Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
calculating averages | Excel Worksheet Functions |