Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
I did not get a response to this question before, so I thought I'd ask again.
Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
instead of average function, you need to do a sumif on the row for cells that
have a value, then divide by a countif on cells that have values. "TLAngelo" wrote: I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
Tania,
Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
Hello Bob, it is calculating the total holding time that it took to process a
document. The spreadsheet currently has 360 rows with the calculations already inserted. So when the employee fills in the data it performs that calculations at that time. So I guess there is "something" in the column that it is trying to average... a calculation. So it won't calculate the average until all those 360 rows are filled in and calculated or deleted. So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data in them, the rest just say ##### in Q and will say that until the data is input. Clear as mudd??!! Tania "Bob Phillips" wrote: Tania, Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
Does this wrk for you
=AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360)) this is an array formula, so after entering it/copying it to the formula bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...} signifying an array formula. -- __________________________________ HTH Bob "TLAngelo" wrote in message ... Hello Bob, it is calculating the total holding time that it took to process a document. The spreadsheet currently has 360 rows with the calculations already inserted. So when the employee fills in the data it performs that calculations at that time. So I guess there is "something" in the column that it is trying to average... a calculation. So it won't calculate the average until all those 360 rows are filled in and calculated or deleted. So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data in them, the rest just say ##### in Q and will say that until the data is input. Clear as mudd??!! Tania "Bob Phillips" wrote: Tania, Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
No, it returns a value of 0.
Tania "Bob Phillips" wrote: Does this wrk for you =AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360)) this is an array formula, so after entering it/copying it to the formula bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...} signifying an array formula. -- __________________________________ HTH Bob "TLAngelo" wrote in message ... Hello Bob, it is calculating the total holding time that it took to process a document. The spreadsheet currently has 360 rows with the calculations already inserted. So when the employee fills in the data it performs that calculations at that time. So I guess there is "something" in the column that it is trying to average... a calculation. So it won't calculate the average until all those 360 rows are filled in and calculated or deleted. So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data in them, the rest just say ##### in Q and will say that until the data is input. Clear as mudd??!! Tania "Bob Phillips" wrote: Tania, Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
What's in Q4:Q360?
Try changing the range to something smaller (Q4:Q10) and do some testing. Remember to array enter the formula, too. TLAngelo wrote: No, it returns a value of 0. Tania "Bob Phillips" wrote: Does this wrk for you =AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360)) this is an array formula, so after entering it/copying it to the formula bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...} signifying an array formula. -- __________________________________ HTH Bob "TLAngelo" wrote in message ... Hello Bob, it is calculating the total holding time that it took to process a document. The spreadsheet currently has 360 rows with the calculations already inserted. So when the employee fills in the data it performs that calculations at that time. So I guess there is "something" in the column that it is trying to average... a calculation. So it won't calculate the average until all those 360 rows are filled in and calculated or deleted. So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data in them, the rest just say ##### in Q and will say that until the data is input. Clear as mudd??!! Tania "Bob Phillips" wrote: Tania, Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
Ok, I tried to make the range something smaller and it still returns 0. I
did enter the array formula by using Ctrl-Shift-Enter. Q4:Q360 has =IF(A30="NA",(0),(NETWORKDAYS(B30,M30,$T$4:$T$9)-1)/2.4+(N30-C30)) Tania "Dave Peterson" wrote: What's in Q4:Q360? Try changing the range to something smaller (Q4:Q10) and do some testing. Remember to array enter the formula, too. TLAngelo wrote: No, it returns a value of 0. Tania "Bob Phillips" wrote: Does this wrk for you =AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360)) this is an array formula, so after entering it/copying it to the formula bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...} signifying an array formula. -- __________________________________ HTH Bob "TLAngelo" wrote in message ... Hello Bob, it is calculating the total holding time that it took to process a document. The spreadsheet currently has 360 rows with the calculations already inserted. So when the employee fills in the data it performs that calculations at that time. So I guess there is "something" in the column that it is trying to average... a calculation. So it won't calculate the average until all those 360 rows are filled in and calculated or deleted. So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data in them, the rest just say ##### in Q and will say that until the data is input. Clear as mudd??!! Tania "Bob Phillips" wrote: Tania, Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Calculations
So it looks like Q4:Q360 could contain nothing but 0's.
In that case, 0 would be the average. What values do you see in Q4:Q10? What do you get returned from this array formula: =AVERAGE(IF(ISNUMBER(Q4:Q10),Q4:Q10)) Tania wrote: Ok, I tried to make the range something smaller and it still returns 0. I did enter the array formula by using Ctrl-Shift-Enter. Q4:Q360 has =IF(A30="NA",(0),(NETWORKDAYS(B30,M30,$T$4:$T$9)-1)/2.4+(N30-C30)) Tania "Dave Peterson" wrote: What's in Q4:Q360? Try changing the range to something smaller (Q4:Q10) and do some testing. Remember to array enter the formula, too. TLAngelo wrote: No, it returns a value of 0. Tania "Bob Phillips" wrote: Does this wrk for you =AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360)) this is an array formula, so after entering it/copying it to the formula bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...} signifying an array formula. -- __________________________________ HTH Bob "TLAngelo" wrote in message ... Hello Bob, it is calculating the total holding time that it took to process a document. The spreadsheet currently has 360 rows with the calculations already inserted. So when the employee fills in the data it performs that calculations at that time. So I guess there is "something" in the column that it is trying to average... a calculation. So it won't calculate the average until all those 360 rows are filled in and calculated or deleted. So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data in them, the rest just say ##### in Q and will say that until the data is input. Clear as mudd??!! Tania "Bob Phillips" wrote: Tania, Average should ignore empty cells, so it sounds as if they are not empty. What is being counted in the average and what is in the other cells? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TLAngelo" wrote in message ... I did not get a response to this question before, so I thought I'd ask again. Does anyone know if this is possible? I have a spreadsheet created that receives data input throughout the month. Once the whole month is filled in and the extra blank rows deleted there is a calcuation at the end that tells the average of row Q. It always shows #### until all the rows are either filled in and/or extra rows deleted. is there a way to have it calculate the average on an ongoing basis and when new data is input it will consider that in the calcualtion as well? Thanks, Tania -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
Average of Multiple Calculations | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |