Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averaging only cells with data
I have a column with 31 rows. In select cells, there is
a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 |
#2
|
|||
|
|||
It will only average the cells with numbers. Make sure the blank cells ARE
blank. -- Ken Russell Remove yourhat to reply by e-mail .. "Randy Lefferts" wrote in message ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 |
#3
|
|||
|
|||
Hi
=AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 |
#4
|
|||
|
|||
Thank you for the replies :)
Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . |
#5
|
|||
|
|||
Hi
sorry, was just a typoe. Try. =AVERAGE(A1:A31) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag ... Thank you for the replies :) Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . |
#6
|
|||
|
|||
Hi Frank,
Yes, sorry about that, that was a typo on my part. Your formula was correct initially, just that when I used that, it did not generate the average I was looking for. It appears to be including the blank cells as a divisor, as well, so that the average is quite lower than it should be. So I reposted thinking that I left out pertinent information that instead of actual numbers in the cells, there were formulas calculating the numbers I need averaged. As an example, the numbers 7.5, 6.5, 4.5, when averaged should equal 6.17, however it is equaling 1.32. So, I need to find a way to average just the values that are appearing within a set of 31 cells. If out of the 31 cells, only 6 contain data, then the numbers contained within the 6 cells should be averaged, not the average of 31 cells with just those 6 values. If, in the 31 rows, there are 25 cells that contain data (generated by the formula), then I would need the average of just those 25 cells. Sorry about not being clearer :( Any chance there is a way to do the above? -----Original Message----- Hi sorry, was just a typoe. Try. =AVERAGE(A1:A31) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:abf301c4d6f3$f2975ee0 ... Thank you for the replies :) Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . . |
#7
|
|||
|
|||
Hi
aVERAGGE ignores empty cell. What exact values do you have in the other cells (zeros?) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag ... Hi Frank, Yes, sorry about that, that was a typo on my part. Your formula was correct initially, just that when I used that, it did not generate the average I was looking for. It appears to be including the blank cells as a divisor, as well, so that the average is quite lower than it should be. So I reposted thinking that I left out pertinent information that instead of actual numbers in the cells, there were formulas calculating the numbers I need averaged. As an example, the numbers 7.5, 6.5, 4.5, when averaged should equal 6.17, however it is equaling 1.32. So, I need to find a way to average just the values that are appearing within a set of 31 cells. If out of the 31 cells, only 6 contain data, then the numbers contained within the 6 cells should be averaged, not the average of 31 cells with just those 6 values. If, in the 31 rows, there are 25 cells that contain data (generated by the formula), then I would need the average of just those 25 cells. Sorry about not being clearer :( Any chance there is a way to do the above? -----Original Message----- Hi sorry, was just a typoe. Try. =AVERAGE(A1:A31) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:abf301c4d6f3$f2975ee0 ... Thank you for the replies :) Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . . |
#8
|
|||
|
|||
In A1:A31, as well as other columns, there is a formula
that will produce a number based on inputs in other cells. So currently there is a formula in the cell, although there is not a value showing, just blank cells. The formula hasn't produced anything yet since there are not any entries in the input cells. This is a timesheet that takes time in and time out (2 time in, 2 time out to cover clocking out at lunch) converts it to hours worked and the formula in a1 calculates the hours worked for the day. So if they don't work on a particular day, while the formula is still in a1, it won't calculate hours worked and as such, should remain blank, which it does. So what is happening is it is treating it as a cell with a value when calculating the hours worked, even though there is not a value there. -----Original Message----- Hi aVERAGGE ignores empty cell. What exact values do you have in the other cells (zeros?) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:a3b801c4d707$fd3fdb60 ... Hi Frank, Yes, sorry about that, that was a typo on my part. Your formula was correct initially, just that when I used that, it did not generate the average I was looking for. It appears to be including the blank cells as a divisor, as well, so that the average is quite lower than it should be. So I reposted thinking that I left out pertinent information that instead of actual numbers in the cells, there were formulas calculating the numbers I need averaged. As an example, the numbers 7.5, 6.5, 4.5, when averaged should equal 6.17, however it is equaling 1.32. So, I need to find a way to average just the values that are appearing within a set of 31 cells. If out of the 31 cells, only 6 contain data, then the numbers contained within the 6 cells should be averaged, not the average of 31 cells with just those 6 values. If, in the 31 rows, there are 25 cells that contain data (generated by the formula), then I would need the average of just those 25 cells. Sorry about not being clearer :( Any chance there is a way to do the above? -----Original Message----- Hi sorry, was just a typoe. Try. =AVERAGE(A1:A31) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:abf301c4d6f3$f2975ee0 ... Thank you for the replies :) Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . . . |
#9
|
|||
|
|||
Hi
I would guess the formula returns zero and this is your issue. Try for example: =SUMIF(A1:A31,"<0")/COUNTIF(A1:A31,"<0") -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag ... In A1:A31, as well as other columns, there is a formula that will produce a number based on inputs in other cells. So currently there is a formula in the cell, although there is not a value showing, just blank cells. The formula hasn't produced anything yet since there are not any entries in the input cells. This is a timesheet that takes time in and time out (2 time in, 2 time out to cover clocking out at lunch) converts it to hours worked and the formula in a1 calculates the hours worked for the day. So if they don't work on a particular day, while the formula is still in a1, it won't calculate hours worked and as such, should remain blank, which it does. So what is happening is it is treating it as a cell with a value when calculating the hours worked, even though there is not a value there. -----Original Message----- Hi aVERAGGE ignores empty cell. What exact values do you have in the other cells (zeros?) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:a3b801c4d707$fd3fdb60 ... Hi Frank, Yes, sorry about that, that was a typo on my part. Your formula was correct initially, just that when I used that, it did not generate the average I was looking for. It appears to be including the blank cells as a divisor, as well, so that the average is quite lower than it should be. So I reposted thinking that I left out pertinent information that instead of actual numbers in the cells, there were formulas calculating the numbers I need averaged. As an example, the numbers 7.5, 6.5, 4.5, when averaged should equal 6.17, however it is equaling 1.32. So, I need to find a way to average just the values that are appearing within a set of 31 cells. If out of the 31 cells, only 6 contain data, then the numbers contained within the 6 cells should be averaged, not the average of 31 cells with just those 6 values. If, in the 31 rows, there are 25 cells that contain data (generated by the formula), then I would need the average of just those 25 cells. Sorry about not being clearer :( Any chance there is a way to do the above? -----Original Message----- Hi sorry, was just a typoe. Try. =AVERAGE(A1:A31) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:abf301c4d6f3$f2975ee0 ... Thank you for the replies :) Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . . . |
#10
|
|||
|
|||
Awesome, thank you very much!
This works exactly how I need it to. Thank you very much for your time today Frank. Sorry I wasn't very clear in the initial post. Thanks again! -----Original Message----- Hi I would guess the formula returns zero and this is your issue. Try for example: =SUMIF(A1:A31,"<0")/COUNTIF(A1:A31,"<0") -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:ad2401c4d70f$44585f20 ... In A1:A31, as well as other columns, there is a formula that will produce a number based on inputs in other cells. So currently there is a formula in the cell, although there is not a value showing, just blank cells. The formula hasn't produced anything yet since there are not any entries in the input cells. This is a timesheet that takes time in and time out (2 time in, 2 time out to cover clocking out at lunch) converts it to hours worked and the formula in a1 calculates the hours worked for the day. So if they don't work on a particular day, while the formula is still in a1, it won't calculate hours worked and as such, should remain blank, which it does. So what is happening is it is treating it as a cell with a value when calculating the hours worked, even though there is not a value there. -----Original Message----- Hi aVERAGGE ignores empty cell. What exact values do you have in the other cells (zeros?) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:a3b801c4d707$fd3fdb60 ... Hi Frank, Yes, sorry about that, that was a typo on my part. Your formula was correct initially, just that when I used that, it did not generate the average I was looking for. It appears to be including the blank cells as a divisor, as well, so that the average is quite lower than it should be. So I reposted thinking that I left out pertinent information that instead of actual numbers in the cells, there were formulas calculating the numbers I need averaged. As an example, the numbers 7.5, 6.5, 4.5, when averaged should equal 6.17, however it is equaling 1.32. So, I need to find a way to average just the values that are appearing within a set of 31 cells. If out of the 31 cells, only 6 contain data, then the numbers contained within the 6 cells should be averaged, not the average of 31 cells with just those 6 values. If, in the 31 rows, there are 25 cells that contain data (generated by the formula), then I would need the average of just those 25 cells. Sorry about not being clearer :( Any chance there is a way to do the above? -----Original Message----- Hi sorry, was just a typoe. Try. =AVERAGE(A1:A31) -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:abf301c4d6f3$f2975ee0 ... Thank you for the replies :) Unfortunately I may not have included enough information since the formula "=AVERAGE(A1:31)" is generating an "incorrect" number. I apologize for not including pertinent information. The column that contains the data that is to be averaged, contains formulas to get the number(s) that I want to average. So instead of A1 containing just a number, it contains the formula =(BN13-BM13)+(BP13-BO13). This is true for each cell in the column that I want to average. Thoughts? -----Original Message----- Hi =AVERAGE(A1:A31) blank cells and strings are ignored -- Regards Frank Kabel Frankfurt, Germany "Randy Lefferts" schrieb im Newsbeitrag news:16d501c4d6e7$46e0bee0 ... I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Formulas in source data | Charts and Charting in Excel | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) | |||
Selected cells grow and data entry impossible EXT is dissabled | Excel Discussion (Misc queries) |