Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
I need to average a row some cells have 0's but I need to include them in
the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
=AVERAGE(B38:M38)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paige06" wrote in message ... I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
I tried that, it doesn't work. It's still not including the 0s
"Bob Phillips" wrote: =AVERAGE(B38:M38) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paige06" wrote in message ... I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
and I forgot to mention I need to skip one column
"Bob Phillips" wrote: =AVERAGE(B38:M38) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paige06" wrote in message ... I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
In which case, specify ranges which don't include that column.
-- David Biddulph "Paige06" wrote in message ... and I forgot to mention I need to skip one column "Bob Phillips" wrote: =AVERAGE(B38:M38) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paige06" wrote in message ... I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
It does if they are real zeroes.
-- __________________________________ HTH Bob "Paige06" wrote in message ... I tried that, it doesn't work. It's still not including the 0s "Bob Phillips" wrote: =AVERAGE(B38:M38) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paige06" wrote in message ... I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
On Jul 30, 9:19*am, Paige06 wrote:
I need to average a row *some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
Thanks Joe,
I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
Ok, I just figured out why I'm getting an incorrect average using example:
average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand????? "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
Paige,
I have a similar issue and this is what I use: IF(SUM($B3:$M3) 0, SUM($B3:$M3) / (COUNT($B3:$M3) - COUNTIF($B3:$M3,0)),"") HTH Harry Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand????? "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
On Jul 30, 11:45 am, Paige06
wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. My understanding is: you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row *some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. *But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). *That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. *I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
I tried both of the formulas you provided, it is still average all 12 months
I will copy one line and show it what I am trying to do. This sheet that i'm averaging is also link from another sheet, so I don't think it will work the way I want it to. But hey this is a good learning experience. Educational Sales 0 1,995 0 - - - - - - - - - 1,995 166 the o and figure and dashes represent a month. the dashes are being counted, in the average. the o's i deleted the link and added them. I'm thinking this is not going to work. "joeu2004" wrote: On Jul 30, 11:45 am, Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. My understanding is: you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
On Jul 31, 7:47 am, Paige06 wrote:
I will copy one line and show it what I am trying to do. Good. This is always good to do when you post here to request assistance. 0 1,995 0 - - - - - - - - - 1,995 166 Well, I think we still have a communication problem. When I create a column of constants that look that -- that is, 3 cells with 0, 1995 and 0 followed by 9 cells with a dash entered as text -- I have no problem computing the 3-cell average with the expression AVERAGE(A1:A12). It correctly computes 665 (1995/3), not 166.25 (1995/12) as your formula does. So my question is: how are you creating the dashes? That is, exactly what formula is in the data cells -- the cells displaying 0, 1995, 0 and dashes? And what is the format of those cells? If they are simply references to another cell, I am asking for the formulas in the target cells and the format of the cells that refer to the target cells. And what exactly is the formula that computed 166 above? For future reference, it is always good to include this information in your original posting. It will expedite your getting a correct solution. ----- original posting ----- On Jul 31, 7:47*am, Paige06 wrote: I tried both of the formulas you provided, it is still average all 12 months I will copy one line and show it what I am trying to do. This sheet that i'm averaging is also link from another sheet, so I don't think it will work the way I want it to. But hey this is a good learning experience. Educational Sales * * * 0 * * * 1,995 * 0 * * * *- * * * - * * * - * * * - * * * - * * * - * * * - * * * - * * * - * * * 1,995 * 166 the o and figure and dashes represent a month. the dashes are being counted, in the average. the o's i deleted the link and added them. I'm thinking this is not going to work. "joeu2004" wrote: On Jul 30, 11:45 am, Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. *Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. *You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. *It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: *if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. *My understanding is: *you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. *For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number.. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row *some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. *But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells.. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). *That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. *I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions.- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
In the original sheet the cell format is number (which I changed from custom
to see if that would make a difference). The cell in which that links to is also number the link to that second sheet from the first is: ='Monthly Sales Chart'!C30. My theory is that in order for it to work with the formula you provided, the cells have to be blank, with no link. Because the months to follow that have no "0's or data yet, but have dashes, it is still calculating them. I hope that I'm making some kind of sense to you. If you were sitting right next to me, it would make sense as to what I'm trying to explain....lol. "joeu2004" wrote: On Jul 31, 7:47 am, Paige06 wrote: I will copy one line and show it what I am trying to do. Good. This is always good to do when you post here to request assistance. 0 1,995 0 - - - - - - - - - 1,995 166 Well, I think we still have a communication problem. When I create a column of constants that look that -- that is, 3 cells with 0, 1995 and 0 followed by 9 cells with a dash entered as text -- I have no problem computing the 3-cell average with the expression AVERAGE(A1:A12). It correctly computes 665 (1995/3), not 166.25 (1995/12) as your formula does. So my question is: how are you creating the dashes? That is, exactly what formula is in the data cells -- the cells displaying 0, 1995, 0 and dashes? And what is the format of those cells? If they are simply references to another cell, I am asking for the formulas in the target cells and the format of the cells that refer to the target cells. And what exactly is the formula that computed 166 above? For future reference, it is always good to include this information in your original posting. It will expedite your getting a correct solution. ----- original posting ----- On Jul 31, 7:47 am, Paige06 wrote: I tried both of the formulas you provided, it is still average all 12 months I will copy one line and show it what I am trying to do. This sheet that i'm averaging is also link from another sheet, so I don't think it will work the way I want it to. But hey this is a good learning experience. Educational Sales 0 1,995 0 - - - - - - - - - 1,995 166 the o and figure and dashes represent a month. the dashes are being counted, in the average. the o's i deleted the link and added them. I'm thinking this is not going to work. "joeu2004" wrote: On Jul 30, 11:45 am, Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. My understanding is: you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number.. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells.. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions.- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
I think I may have figured it out. What format can I use in the 2nd sheet
that has the links, that will leave the cell blank other than the link?The dashes are there because of the format I have it set on for the link. So, If I could just get a blank cell with the link it would do what I want it to do. "Paige06" wrote: In the original sheet the cell format is number (which I changed from custom to see if that would make a difference). The cell in which that links to is also number the link to that second sheet from the first is: ='Monthly Sales Chart'!C30. My theory is that in order for it to work with the formula you provided, the cells have to be blank, with no link. Because the months to follow that have no "0's or data yet, but have dashes, it is still calculating them. I hope that I'm making some kind of sense to you. If you were sitting right next to me, it would make sense as to what I'm trying to explain....lol. "joeu2004" wrote: On Jul 31, 7:47 am, Paige06 wrote: I will copy one line and show it what I am trying to do. Good. This is always good to do when you post here to request assistance. 0 1,995 0 - - - - - - - - - 1,995 166 Well, I think we still have a communication problem. When I create a column of constants that look that -- that is, 3 cells with 0, 1995 and 0 followed by 9 cells with a dash entered as text -- I have no problem computing the 3-cell average with the expression AVERAGE(A1:A12). It correctly computes 665 (1995/3), not 166.25 (1995/12) as your formula does. So my question is: how are you creating the dashes? That is, exactly what formula is in the data cells -- the cells displaying 0, 1995, 0 and dashes? And what is the format of those cells? If they are simply references to another cell, I am asking for the formulas in the target cells and the format of the cells that refer to the target cells. And what exactly is the formula that computed 166 above? For future reference, it is always good to include this information in your original posting. It will expedite your getting a correct solution. ----- original posting ----- On Jul 31, 7:47 am, Paige06 wrote: I tried both of the formulas you provided, it is still average all 12 months I will copy one line and show it what I am trying to do. This sheet that i'm averaging is also link from another sheet, so I don't think it will work the way I want it to. But hey this is a good learning experience. Educational Sales 0 1,995 0 - - - - - - - - - 1,995 166 the o and figure and dashes represent a month. the dashes are being counted, in the average. the o's i deleted the link and added them. I'm thinking this is not going to work. "joeu2004" wrote: On Jul 30, 11:45 am, Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. My understanding is: you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number.. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells.. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions.- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
On Jul 31, 11:44*am, Paige06
wrote: I think I may have figured it out. What format can I use in the 2nd sheet that has the links, that will leave the cell blank other than the link?The dashes are there because of the format I have it set on for the link. So, If I could just get a blank cell with the link it would do what I want it to do. I think you need to understand that there is a difference between a cell that __appears__ to be blank because of some format trick v. a cell whose __value__ is truly blank (the null string "") or empty. If you want to use the simple formula AVERAGE(B38:M38), the __value__, not just the appearance, of the cells that should be excluded from the average must be empty or some text, which could be the null string ("") to appear blank, or it could be an explicit dash if you prefer. (In the latter case, you might want to set the Horizontal Alignment to be Right so that the dash aligns with the rightmost digits of numbers.) But this must be accomplished by using a formula or constant, not a format. For example, if B37:M37 contains the dates 1/1/2008, 1/2/2008 etc, which you can format to show just the month or month and year if you like, then B38 might have the formula: =if(month(B37) < month(today()), 'Monthly Sales Chart'!C30, "-") Alternatively, the text (null string or dash) could be in 'Monthly Sales Chart'!C30 -- again, as a constant or formula, not a format trick. In that case, the simple AVERAGE() function should work with your original referring formula in B38, namely: ='Monthly Sales Chart'!C30 Does this help? PS: Instead of using TODAY() in those formulas, it would be prudent to put the date of the worksheet in some cell and refer to it. And instead of using TODAY() explicitly even there, you can use a keystroke operation to insert the current date into the cell as a constant when you are ready to update the worksheet. ----- original posting ----- On Jul 31, 11:44 am, Paige06 wrote: I think I may have figured it out. What format can I use in the 2nd sheet that has the links, that will leave the cell blank other than the link?The dashes are there because of the format I have it set on for the link. So, If I could just get a blank cell with the link it would do what I want it to do. "Paige06" wrote: In the original sheet the cell format is number (which I changed from custom to see if that would make a difference). The cell in which that links to is also number the link to that second sheet from the first is: ='Monthly Sales Chart'!C30. My theory is that in order for it to work with the formula you provided, the cells have to be blank, with no link. Because the months to follow that have no "0's or data yet, but have dashes, it is still calculating them. I hope that I'm making some kind of sense to you. If you were sitting right next to me, it would make sense as to what I'm trying to explain....lol. "joeu2004" wrote: On Jul 31, 7:47 am, Paige06 wrote: I will copy one line and show it what I am trying to do. Good. *This is always good to do when you post here to request assistance. 0 *1,995 *0 *- *- *- *- *- *- *- *- *- *1,995 *166 Well, I think we still have a communication problem. *When I create a column of constants that look that -- that is, 3 cells with 0, 1995 and 0 followed by 9 cells with a dash entered as text -- I have no problem computing the 3-cell average with the expression AVERAGE(A1:A12). *It correctly computes 665 (1995/3), not 166.25 (1995/12) as your formula does. So my question is: *how are you creating the dashes? That is, exactly what formula is in the data cells -- the cells displaying 0, 1995, 0 and dashes? *And what is the format of those cells? If they are simply references to another cell, I am asking for the formulas in the target cells and the format of the cells that refer to the target cells. And what exactly is the formula that computed 166 above? For future reference, it is always good to include this information in your original posting. *It will expedite your getting a correct solution. ----- original posting ----- On Jul 31, 7:47 am, Paige06 wrote: I tried both of the formulas you provided, it is still average all 12 months I will copy one line and show it what I am trying to do. This sheet that i'm averaging is also link from another sheet, so I don't think it will work the way I want it to. But hey this is a good learning experience. Educational Sales * * * 0 * * * 1,995 * 0 * * * *- * * * - * * * - * * * - * * * - * * * - * * * - * * * - * * * - * * * 1,995 * 166 the o and figure and dashes represent a month. the dashes are being counted, in the average. the o's i deleted the link and added them. I'm thinking this is not going to work. "joeu2004" wrote: On Jul 30, 11:45 am, Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. *Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. *You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. *It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: *if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. *My understanding is: *you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. *For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number.. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row *some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. *But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells.. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). *That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. *I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
I appreciate all the time you spent on this with me. I will try to do what
you explained. If I have any other questions I will post it. Thank you for your help "joeu2004" wrote: On Jul 31, 11:44 am, Paige06 wrote: I think I may have figured it out. What format can I use in the 2nd sheet that has the links, that will leave the cell blank other than the link?The dashes are there because of the format I have it set on for the link. So, If I could just get a blank cell with the link it would do what I want it to do. I think you need to understand that there is a difference between a cell that __appears__ to be blank because of some format trick v. a cell whose __value__ is truly blank (the null string "") or empty. If you want to use the simple formula AVERAGE(B38:M38), the __value__, not just the appearance, of the cells that should be excluded from the average must be empty or some text, which could be the null string ("") to appear blank, or it could be an explicit dash if you prefer. (In the latter case, you might want to set the Horizontal Alignment to be Right so that the dash aligns with the rightmost digits of numbers.) But this must be accomplished by using a formula or constant, not a format. For example, if B37:M37 contains the dates 1/1/2008, 1/2/2008 etc, which you can format to show just the month or month and year if you like, then B38 might have the formula: =if(month(B37) < month(today()), 'Monthly Sales Chart'!C30, "-") Alternatively, the text (null string or dash) could be in 'Monthly Sales Chart'!C30 -- again, as a constant or formula, not a format trick. In that case, the simple AVERAGE() function should work with your original referring formula in B38, namely: ='Monthly Sales Chart'!C30 Does this help? PS: Instead of using TODAY() in those formulas, it would be prudent to put the date of the worksheet in some cell and refer to it. And instead of using TODAY() explicitly even there, you can use a keystroke operation to insert the current date into the cell as a constant when you are ready to update the worksheet. ----- original posting ----- On Jul 31, 11:44 am, Paige06 wrote: I think I may have figured it out. What format can I use in the 2nd sheet that has the links, that will leave the cell blank other than the link?The dashes are there because of the format I have it set on for the link. So, If I could just get a blank cell with the link it would do what I want it to do. "Paige06" wrote: In the original sheet the cell format is number (which I changed from custom to see if that would make a difference). The cell in which that links to is also number the link to that second sheet from the first is: ='Monthly Sales Chart'!C30. My theory is that in order for it to work with the formula you provided, the cells have to be blank, with no link. Because the months to follow that have no "0's or data yet, but have dashes, it is still calculating them. I hope that I'm making some kind of sense to you. If you were sitting right next to me, it would make sense as to what I'm trying to explain....lol. "joeu2004" wrote: On Jul 31, 7:47 am, Paige06 wrote: I will copy one line and show it what I am trying to do. Good. This is always good to do when you post here to request assistance. 0 1,995 0 - - - - - - - - - 1,995 166 Well, I think we still have a communication problem. When I create a column of constants that look that -- that is, 3 cells with 0, 1995 and 0 followed by 9 cells with a dash entered as text -- I have no problem computing the 3-cell average with the expression AVERAGE(A1:A12). It correctly computes 665 (1995/3), not 166.25 (1995/12) as your formula does. So my question is: how are you creating the dashes? That is, exactly what formula is in the data cells -- the cells displaying 0, 1995, 0 and dashes? And what is the format of those cells? If they are simply references to another cell, I am asking for the formulas in the target cells and the format of the cells that refer to the target cells. And what exactly is the formula that computed 166 above? For future reference, it is always good to include this information in your original posting. It will expedite your getting a correct solution. ----- original posting ----- On Jul 31, 7:47 am, Paige06 wrote: I tried both of the formulas you provided, it is still average all 12 months I will copy one line and show it what I am trying to do. This sheet that i'm averaging is also link from another sheet, so I don't think it will work the way I want it to. But hey this is a good learning experience. Educational Sales 0 1,995 0 - - - - - - - - - 1,995 166 the o and figure and dashes represent a month. the dashes are being counted, in the average. the o's i deleted the link and added them. I'm thinking this is not going to work. "joeu2004" wrote: On Jul 30, 11:45 am, Paige06 wrote: Ok, I just figured out why I'm getting an incorrect average using example: average(B38:M38), because it's taking all 12 months and averaging. I do not want that. I want it to average as I enter numbers or not in month to month. Understand? Forgive me if I'm being anal, but I still do not understand. Here is what I understand the problem to be.... You are averaging all 12 months, but only the first "n" months have valid data. You want to average only the first "n" months, which might include zero-valued sales. Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__ either the cells after the first "n" cells are empty (no formula and no constant value), or they contain a formula that evaluates to the null string. I suspect you are entering zero into those cells. It would be better to leave them empty. The problem with any formula that tries to exclude zero-valued cells from the average is: if sales are actually zero in one of the first "n" months, you will inadvertently exclude those cells from the average. My understanding is: you don't want to exclude those "valid" zero values. If you "must" have zero represent both zero sales and "no data", you need to test some other condition to distinguish the two. For example, use the following array formula (commit with ctrl-shift- Enter, not just Enter; if necessary, re-enter by pressing F2 followed by ctrl-shift-Enter): =AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38)) Alternatively, use the following non-array formula: =SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) ) The "--" is a trick to make the boolean expression be treated as the number 1 or 0. Doesn't that help? ----- original posting ----- On Jul 30, 11:30 am, Paige06 wrote: Thanks Joe, I will try to explain again more clearly. I was a little frazzeled when I wrote that. I have 1 main sheet that I enter number into each month. From that sheet I have each cell for each sales guy linked into the 2nd sheet. On the second sheet for each month I want an average of sales numbers calculating as I enter numbers each month. I wasn't aware that the orginal formula I had excluded 0. I have tried to change the formula and it still will not calculate each month even with the 0's, only the month with the number amount. And if I try to use =average A1:A15 it gives a wrong number.. But if I open a new sheet it works the way I want it to. ???????? I'm not by any means an excel expert, but I do know a little. And yes, my formula was an array.. "joeu2004" wrote: On Jul 30, 9:19 am, Paige06 wrote: I need to average a row some cells have 0's but I need to include them in the function so it averages correctly. The formula I currently have is this =AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0, april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any suggestions????? You intentions are unclear to me. First, your formula says that you want to exclude zero-valued cells from the average. But your first statement says "I need to include them", and your example ("divided by 4") and your intended result (7.5) suggests that you do indeed want to include zero-valued cells.. Second, from the presentation of the example, it is unclear to me whether the range B38:M38 includes just cells with numeric values (e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g. literally "jan", 5, "feb", 7, "mar", 0, "april", 18). That may or may not have anything to do with your problem. Finally, you provide nothing in your example that would explain how your formula returns 3. I suspect the value of 3 is coming from different example, not the example supplied in your posting. Having said that, looking only at the syntax of the example formula above, did you remember to enter it as an array formula (commit with ctrl-shift-Enter, not just Enter)? If you do not, then the formula above is logically equivalent to: =if(B38<0, average(B38:M38), 0) That may or not may not be the root cause of your problem, given that I do not understand your intentions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averages | Excel Discussion (Misc queries) | |||
Help with Averages | Excel Worksheet Functions | |||
Averages | Excel Worksheet Functions | |||
Averages | Excel Worksheet Functions | |||
averages | Excel Worksheet Functions |