| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Function that does not include 0
I have a spreadsheet that has dates on the rows and daily counts for
coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that does not include 0
I'm sorry..not a SUM but an AVERAGE
"Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
=SUM(B2:K2)/COUNTIF(B2:K2,"0")
Hope this helps. -- John C "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
What if the call number is actually a "0" for the day?
"John C" wrote: =SUM(B2:K2)/COUNTIF(B2:K2,"0") Hope this helps. -- John C "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
Well, you were expecting a value of 4, that is why I did it that way, since
you have a total of 22 calls over 6 days (the 4th having 0 calls), would actually be less than 4. That being said, why do days that have yet to occur have any value in them? -- John C "Txlonghorn76" wrote: What if the call number is actually a "0" for the day? "John C" wrote: =SUM(B2:K2)/COUNTIF(B2:K2,"0") Hope this helps. -- John C "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
Hi,
From your data and using the standard =AVERAGE(B2:K2) I get 2.2 A formula that ignores zero =AVERAGE(IF(B2:K20,B2:K2)) returns 4.4 so maybe you want =INT(AVERAGE(IF(B2:K20,B2:K2))) the average(if is an array formula and must ben entered using CTRL+Shift+Enter and not just enter. If you do it correctly Excel will put curly brackets around it{}. you can't type these yourself. Mike "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
formula using the crl+shift+ enter, I get #DIV/0!
"Mike H" wrote: Hi, From your data and using the standard =AVERAGE(B2:K2) I get 2.2 A formula that ignores zero =AVERAGE(IF(B2:K20,B2:K2)) returns 4.4 so maybe you want =INT(AVERAGE(IF(B2:K20,B2:K2))) the average(if is an array formula and must ben entered using CTRL+Shift+Enter and not just enter. If you do it correctly Excel will put curly brackets around it{}. you can't type these yourself. Mike "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
Hi,
If you getting #Div/0! then are there any numbers in the range and are they really numbers and not just text that looks like numbers. Mike "Txlonghorn76" wrote: formula using the crl+shift+ enter, I get #DIV/0! "Mike H" wrote: Hi, From your data and using the standard =AVERAGE(B2:K2) I get 2.2 A formula that ignores zero =AVERAGE(IF(B2:K20,B2:K2)) returns 4.4 so maybe you want =INT(AVERAGE(IF(B2:K20,B2:K2))) the average(if is an array formula and must ben entered using CTRL+Shift+Enter and not just enter. If you do it correctly Excel will put curly brackets around it{}. you can't type these yourself. Mike "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
i got the formula to work but i want it to only average up to today's date.
"Mike H" wrote: Hi, If you getting #Div/0! then are there any numbers in the range and are they really numbers and not just text that looks like numbers. Mike "Txlonghorn76" wrote: formula using the crl+shift+ enter, I get #DIV/0! "Mike H" wrote: Hi, From your data and using the standard =AVERAGE(B2:K2) I get 2.2 A formula that ignores zero =AVERAGE(IF(B2:K20,B2:K2)) returns 4.4 so maybe you want =INT(AVERAGE(IF(B2:K20,B2:K2))) the average(if is an array formula and must ben entered using CTRL+Shift+Enter and not just enter. If you do it correctly Excel will put curly brackets around it{}. you can't type these yourself. Mike "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Function that does not include 0
I posted in other part of thread, by why are there 0's in days that have yet
to occur? How is the data here tabulated? You could just modify the formulas that tabulate the daily totals. i.e.: =IF(TODAY()=B1,SUM(yourdata),"") This would be in cell B2 for example, and then just copy across. -- John C "Txlonghorn76" wrote: i got the formula to work but i want it to only average up to today's date. "Mike H" wrote: Hi, If you getting #Div/0! then are there any numbers in the range and are they really numbers and not just text that looks like numbers. Mike "Txlonghorn76" wrote: formula using the crl+shift+ enter, I get #DIV/0! "Mike H" wrote: Hi, From your data and using the standard =AVERAGE(B2:K2) I get 2.2 A formula that ignores zero =AVERAGE(IF(B2:K20,B2:K2)) returns 4.4 so maybe you want =INT(AVERAGE(IF(B2:K20,B2:K2))) the average(if is an array formula and must ben entered using CTRL+Shift+Enter and not just enter. If you do it correctly Excel will put curly brackets around it{}. you can't type these yourself. Mike "Txlonghorn76" wrote: I have a spreadsheet that has dates on the rows and daily counts for coworkers in the columns. These numbers are pulled by Hlookup formulas in the cells that pull from another worksheet. I want to have a running month-to-date average without including the days that haven't it average the days that haven't occurred. The current formula I'm using =SUM(B2:K2) gives me and output of 2 but I'm looking for an output of 4. 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct 08-Oct 09-Oct 10-Oct Calls 1 7 6 0 3 5 0 0 0 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I include both AND and OR in the same function? | Excel Worksheet Functions | |||
| Date Function to include a day and automatically updated each new | Excel Worksheet Functions | |||
| how do I include advance payments in the PMT function | Excel Worksheet Functions | |||
| Can SUMIF function include AND function | Excel Worksheet Functions | |||
| Include Saturday in the WORKDAY function | Excel Worksheet Functions | |||
Hybrid Mode
