|
Average if ...
What formula do I use to find the average of certain columns, when columns
around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 |
Average if ...
Try this array formula** which will work in all versions of Excel:
=AVERAGE(IF((A1:A5="no")*(B1:B5="full"),D1:D5)) Try this normally entered version if you're using Excel 2007 only: =AVERAGEIFS(D1:D5,A1:A5,"no",B1:B5,"full") -- Biff Microsoft Excel MVP "taurus99" wrote in message ... What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 |
Average if ...
Forgot something:
Try this array formula** ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** which will work in all versions of Excel: =AVERAGE(IF((A1:A5="no")*(B1:B5="full"),D1:D5)) Try this normally entered version if you're using Excel 2007 only: =AVERAGEIFS(D1:D5,A1:A5,"no",B1:B5,"full") -- Biff Microsoft Excel MVP "taurus99" wrote in message ... What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 |
Average if ...
taurus99 wrote:
What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 =average(if(--(a1:a5="no"),--(b1:b5="full"),d1:d5)) |
Average if ...
smartin wrote:
taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
Thanks, smartin, that worked!!
One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
Glad it worked!
You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT
ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))
If that returns the correct result then it's just a coincidence. Try it like this (array entered): =AVERAGE(IF((A1:A5="no")*(B1:B5="full"),C1:C5)) -- Biff Microsoft Excel MVP "smartin" wrote in message ... smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))
Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
I copied that exact formula in and got a #DIV/0! error
D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
Remove the quotes from around the 0:
=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
T. Valko wrote:
=average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) If that returns the correct result then it's just a coincidence. Try it like this (array entered): =AVERAGE(IF((A1:A5="no")*(B1:B5="full"),C1:C5)) Definitely, I was not correct. Apologies, and thanks Biff for catching my mistake. |
Average if ...
There are no empty cells, but that is good to know.
It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
Nevermind, I figured it out. WOO HOO
Thanks, you have been a huge help!! "taurus99" wrote: There are no empty cells, but that is good to know. It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "taurus99" wrote in message ... Nevermind, I figured it out. WOO HOO Thanks, you have been a huge help!! "taurus99" wrote: There are no empty cells, but that is good to know. It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
Ok, so while I have you still (and I am learning so much tonight) ... I came
upon another scenerio. What if I wanted to include one or two more text words in a column. In this example: Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or "closed". (I need the average of D2, D4 and D7) A B C D 1 yes full open 27 2 no full partial 15 3 yes empty closed 31 4 no full open 19 5 no empty open 16 6 yes full closed 7 7 no full closed 10 In my real formula I put this, which I KNOW is wrong! LOL E is the column which has 3 different items of text. =AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000)) "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Nevermind, I figured it out. WOO HOO Thanks, you have been a huge help!! "taurus99" wrote: There are no empty cells, but that is good to know. It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
C = "open", "partial", or "closed".
If those 3 are the only possible entries then all you need to do is check and make sure column C isn't empty: =AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(C1:C7<"" ),D1:D7)) If there are more possible entries than those 3... List the variables in a range of cells: H1 = open H2 = partial H3 = closed Then: =AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(ISNUMBER( MATCH(C1:C7,H1:H3,0))),D1:D7)) Don't forget, array entered! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Ok, so while I have you still (and I am learning so much tonight) ... I came upon another scenerio. What if I wanted to include one or two more text words in a column. In this example: Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or "closed". (I need the average of D2, D4 and D7) A B C D 1 yes full open 27 2 no full partial 15 3 yes empty closed 31 4 no full open 19 5 no empty open 16 6 yes full closed 7 7 no full closed 10 In my real formula I put this, which I KNOW is wrong! LOL E is the column which has 3 different items of text. =AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000)) "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Nevermind, I figured it out. WOO HOO Thanks, you have been a huge help!! "taurus99" wrote: There are no empty cells, but that is good to know. It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
Unbelievable! Worked perfectly.
You are a life saver!!!!!! Thank you so much and have a great day! "T. Valko" wrote: C = "open", "partial", or "closed". If those 3 are the only possible entries then all you need to do is check and make sure column C isn't empty: =AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(C1:C7<"" ),D1:D7)) If there are more possible entries than those 3... List the variables in a range of cells: H1 = open H2 = partial H3 = closed Then: =AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(ISNUMBER( MATCH(C1:C7,H1:H3,0))),D1:D7)) Don't forget, array entered! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Ok, so while I have you still (and I am learning so much tonight) ... I came upon another scenerio. What if I wanted to include one or two more text words in a column. In this example: Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or "closed". (I need the average of D2, D4 and D7) A B C D 1 yes full open 27 2 no full partial 15 3 yes empty closed 31 4 no full open 19 5 no empty open 16 6 yes full closed 7 7 no full closed 10 In my real formula I put this, which I KNOW is wrong! LOL E is the column which has 3 different items of text. =AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000)) "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Nevermind, I figured it out. WOO HOO Thanks, you have been a huge help!! "taurus99" wrote: There are no empty cells, but that is good to know. It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
Average if ...
You're quite welcome!
-- Biff Microsoft Excel MVP "taurus99" wrote in message ... Unbelievable! Worked perfectly. You are a life saver!!!!!! Thank you so much and have a great day! "T. Valko" wrote: C = "open", "partial", or "closed". If those 3 are the only possible entries then all you need to do is check and make sure column C isn't empty: =AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(C1:C7<"" ),D1:D7)) If there are more possible entries than those 3... List the variables in a range of cells: H1 = open H2 = partial H3 = closed Then: =AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(ISNUMBER( MATCH(C1:C7,H1:H3,0))),D1:D7)) Don't forget, array entered! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Ok, so while I have you still (and I am learning so much tonight) ... I came upon another scenerio. What if I wanted to include one or two more text words in a column. In this example: Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or "closed". (I need the average of D2, D4 and D7) A B C D 1 yes full open 27 2 no full partial 15 3 yes empty closed 31 4 no full open 19 5 no empty open 16 6 yes full closed 7 7 no full closed 10 In my real formula I put this, which I KNOW is wrong! LOL E is the column which has 3 different items of text. =AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000)) "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "taurus99" wrote in message ... Nevermind, I figured it out. WOO HOO Thanks, you have been a huge help!! "taurus99" wrote: There are no empty cells, but that is good to know. It worked PERFECTLY! Thanks!! So, one more final question ... what is this array stuff all about? I noticed I can't just copy my formula down the page and have excel change the values for me like other formulas ... "T. Valko" wrote: Remove the quotes from around the 0: =AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000)) Make sure you array enter. When you quote numbers, "0", Excel evaluates them as TEXT. Now, this leads to another possible glitch. An *empty cell* will evaluate to 0. To exclude empty cells from be evaluated (if they might be present) you need to add another test: =AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000)) -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I copied that exact formula in and got a #DIV/0! error D = numeric 0 E = Text FTFB G = Text Y "T. Valko" wrote: AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) Try it like this (array entered): =AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000)) Does D55:D5000 contain TEXT 0 or numeric 0? In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the same. -- Biff Microsoft Excel MVP "taurus99" wrote in message ... I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT ENTER I got an error message that reads: "You've entered too many arguments for this function" Any suggestions? This is my formula ... (on my real spreadsheet) =AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000)) It's works just fine with just the "0" and "FTFB" but when I add the 3rd one "Y", it won't work. "smartin" wrote: Glad it worked! You can just add your new constraint to the IF condition: =average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5)) Again, this is an array formula, so Ctrl+Shift+Enter. taurus99 wrote: Thanks, smartin, that worked!! One more question, what if I wanted to add one more column in there? The average of Column D, ONLY IF A equals "no" and B equals "full" and C equals "open". A B C D 1 yes full open 27 2 no full open 15 3 yes empty closed 31 4 no full open 16 5 no empty open 16 "smartin" wrote: smartin wrote: taurus99 wrote: What formula do I use to find the average of certain columns, when columns around it equal specific text? example: I need the average of Column D, ONLY IF A equals "no" and B equals "full". (in this case the average of D2 and D4) A B C D 1 yes full 0 27 2 no full 3 15 3 yes empty 9 31 4 no full 1 16 5 no empty 7 16 Oops I should have written =average(if((a1:a5="no"),(b1:b5="full"),d1:d5)) and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.) |
| All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com