| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
average if zero
I'm looking for a formula similar to the following, but one that works.
=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#2
|
|||
|
|||
|
substitute * for +
=average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#3
|
|||
|
|||
|
I've swapped out * for + but the zeros are still being calculated in and
reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#4
|
|||
|
|||
|
Did you remember to make it an array (ctrl-shift-enter)?
I tested it. It works. HTH, Gary Brown "Lori Hornick" wrote: I've swapped out * for + but the zeros are still being calculated in and reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#5
|
|||
|
|||
|
Yes, if I don't use array, I receive #DIV/0!
If I do use array, it results in an average including zeros. Sigh!!! I've just thrown together a baby spreadsheet that looks like this: B C 4 PW 20% 5 JC 30% 6 PW 0% =AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6)) The result is 10%, not 20% "Gary Brown" wrote: Did you remember to make it an array (ctrl-shift-enter)? I tested it. It works. HTH, Gary Brown "Lori Hornick" wrote: I've swapped out * for + but the zeros are still being calculated in and reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#6
|
|||
|
|||
|
Lori, In array formulas, + acts as OR, * acts as AND. You want AND, so use =AVERAGE(IF((B4:B6="PW")*(C4:C6"0"),C4:C6)) And since you are looking at numbers, get rid of the quotes =AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6)) And you may also be looking at rounded numbers, so try =AVERAGE(IF((B4:B6="PW")*(C4:C6=0.005),C4:C6)) HTH, Bernie MS Excel MVP "Lori Hornick" wrote in message ... Yes, if I don't use array, I receive #DIV/0! If I do use array, it results in an average including zeros. Sigh!!! I've just thrown together a baby spreadsheet that looks like this: B C 4 PW 20% 5 JC 30% 6 PW 0% =AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6)) The result is 10%, not 20% "Gary Brown" wrote: Did you remember to make it an array (ctrl-shift-enter)? I tested it. It works. HTH, Gary Brown "Lori Hornick" wrote: I've swapped out * for + but the zeros are still being calculated in and reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#7
|
|||
|
|||
|
This is perfect; it did the trick. Thanks for all the help and the
distinction between * and + and the "" Knowing this made all the difference. "Bernie Deitrick" wrote: Lori, In array formulas, + acts as OR, * acts as AND. You want AND, so use =AVERAGE(IF((B4:B6="PW")*(C4:C6"0"),C4:C6)) And since you are looking at numbers, get rid of the quotes =AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6)) And you may also be looking at rounded numbers, so try =AVERAGE(IF((B4:B6="PW")*(C4:C6=0.005),C4:C6)) HTH, Bernie MS Excel MVP "Lori Hornick" wrote in message ... Yes, if I don't use array, I receive #DIV/0! If I do use array, it results in an average including zeros. Sigh!!! I've just thrown together a baby spreadsheet that looks like this: B C 4 PW 20% 5 JC 30% 6 PW 0% =AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6)) The result is 10%, not 20% "Gary Brown" wrote: Did you remember to make it an array (ctrl-shift-enter)? I tested it. It works. HTH, Gary Brown "Lori Hornick" wrote: I've swapped out * for + but the zeros are still being calculated in and reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#8
|
|||
|
|||
|
Lori,
Glad to hear it worked, but in my first post, I used =AVERAGE(IF((A1:A4="Jones")*(C1:C40),C1:C4)) which is essentially identical to =AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6)) What made the difference? Bernie MS Excel MVP "Lori Hornick" wrote in message ... This is perfect; it did the trick. Thanks for all the help and the distinction between * and + and the "" Knowing this made all the difference. "Bernie Deitrick" wrote: Lori, In array formulas, + acts as OR, * acts as AND. You want AND, so use =AVERAGE(IF((B4:B6="PW")*(C4:C6"0"),C4:C6)) And since you are looking at numbers, get rid of the quotes =AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6)) And you may also be looking at rounded numbers, so try =AVERAGE(IF((B4:B6="PW")*(C4:C6=0.005),C4:C6)) HTH, Bernie MS Excel MVP "Lori Hornick" wrote in message ... Yes, if I don't use array, I receive #DIV/0! If I do use array, it results in an average including zeros. Sigh!!! I've just thrown together a baby spreadsheet that looks like this: B C 4 PW 20% 5 JC 30% 6 PW 0% =AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6)) The result is 10%, not 20% "Gary Brown" wrote: Did you remember to make it an array (ctrl-shift-enter)? I tested it. It works. HTH, Gary Brown "Lori Hornick" wrote: I've swapped out * for + but the zeros are still being calculated in and reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
|
#9
|
|||
|
|||
|
Probably a posting by another person in a duplicate thread indicating that I
should replace the * with a +. In that process quotes were inserted automatically by Excel (maybe by not using an array command) and remained there so when I reverted back to the * Excel didn't read the formula at all. "Bernie Deitrick" wrote: Lori, Glad to hear it worked, but in my first post, I used =AVERAGE(IF((A1:A4="Jones")*(C1:C40),C1:C4)) which is essentially identical to =AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6)) What made the difference? Bernie MS Excel MVP "Lori Hornick" wrote in message ... This is perfect; it did the trick. Thanks for all the help and the distinction between * and + and the "" Knowing this made all the difference. "Bernie Deitrick" wrote: Lori, In array formulas, + acts as OR, * acts as AND. You want AND, so use =AVERAGE(IF((B4:B6="PW")*(C4:C6"0"),C4:C6)) And since you are looking at numbers, get rid of the quotes =AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6)) And you may also be looking at rounded numbers, so try =AVERAGE(IF((B4:B6="PW")*(C4:C6=0.005),C4:C6)) HTH, Bernie MS Excel MVP "Lori Hornick" wrote in message ... Yes, if I don't use array, I receive #DIV/0! If I do use array, it results in an average including zeros. Sigh!!! I've just thrown together a baby spreadsheet that looks like this: B C 4 PW 20% 5 JC 30% 6 PW 0% =AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6)) The result is 10%, not 20% "Gary Brown" wrote: Did you remember to make it an array (ctrl-shift-enter)? I tested it. It works. HTH, Gary Brown "Lori Hornick" wrote: I've swapped out * for + but the zeros are still being calculated in and reducing the average I need to see. "Gary Brown" wrote: substitute * for + =average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4)) HTH, Gary Brown "Lori Hornick" wrote: I'm looking for a formula similar to the following, but one that works. =average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4)) Column A represent sales rep column, and I want to see the average sales for all cells that have data entered in column C. I've tried the ctrl+shift+enter, but it's still averaging with zeros, so something is wrong with my formula. Thank you. -- Lori |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average the Last Five Cells in a Column | Excel Worksheet Functions | |||
| Plotting moving average line on a chart | Charts and Charting in Excel | |||
| Average (geometric) | Excel Worksheet Functions | |||
| Average | Excel Worksheet Functions | |||
| average, array and offsets | Excel Worksheet Functions | |||
Linear Mode
