Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default Sum Function that does not include 0

This is what the sheet currently looks like:

MTD Average 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct
08-Oct 09-Oct 10-Oct
=Average(B2:K2) 1 7 6 0 3 5 0 0 0 0

I hope this makes more sense, I should have posted this from the beginning.
I am trying to have one formual so we don't have to change it everyday. Can I
combine =today()-1 with the average somehow?

"John C" wrote:

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

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default Sum Function that does not include 0

My question is: Where do the values come from as far as number of calls? In
other words, why is there a 0 for Oct 8, 9, and 10? When those dates are
obviously not here yet. If these are manually entered, why are they entered
before the date has come to pass?
--
John C


"Txlonghorn76" wrote:

This is what the sheet currently looks like:

MTD Average 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct
08-Oct 09-Oct 10-Oct
=Average(B2:K2) 1 7 6 0 3 5 0 0 0 0

I hope this makes more sense, I should have posted this from the beginning.
I am trying to have one formual so we don't have to change it everyday. Can I
combine =today()-1 with the average somehow?

"John C" wrote:

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

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default Sum Function that does not include 0

All of the contents of the cells are Hlookup formulas that lead to a main
data sheet. Once the main data sheet is updated, we open this worksheet & it
will automatically update the cells for yesterdays numbers.

"John C" wrote:

My question is: Where do the values come from as far as number of calls? In
other words, why is there a 0 for Oct 8, 9, and 10? When those dates are
obviously not here yet. If these are manually entered, why are they entered
before the date has come to pass?
--
John C


"Txlonghorn76" wrote:

This is what the sheet currently looks like:

MTD Average 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct
08-Oct 09-Oct 10-Oct
=Average(B2:K2) 1 7 6 0 3 5 0 0 0 0

I hope this makes more sense, I should have posted this from the beginning.
I am trying to have one formual so we don't have to change it everyday. Can I
combine =today()-1 with the average somehow?

"John C" wrote:

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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default Sum Function that does not include 0

Then why not have your formulas be like the following in row 2, addressing
the dates listed in row 1:
=IF(TODAY()=B1,HLOOKUP(....,....,....,....),"")
--
John C


"Txlonghorn76" wrote:

All of the contents of the cells are Hlookup formulas that lead to a main
data sheet. Once the main data sheet is updated, we open this worksheet & it
will automatically update the cells for yesterdays numbers.

"John C" wrote:

My question is: Where do the values come from as far as number of calls? In
other words, why is there a 0 for Oct 8, 9, and 10? When those dates are
obviously not here yet. If these are manually entered, why are they entered
before the date has come to pass?
--
John C


"Txlonghorn76" wrote:

This is what the sheet currently looks like:

MTD Average 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct
08-Oct 09-Oct 10-Oct
=Average(B2:K2) 1 7 6 0 3 5 0 0 0 0

I hope this makes more sense, I should have posted this from the beginning.
I am trying to have one formual so we don't have to change it everyday. Can I
combine =today()-1 with the average somehow?

"John C" wrote:

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

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default Sum Function that does not include 0

That worked wonderfully! Thanks!

"John C" wrote:

Then why not have your formulas be like the following in row 2, addressing
the dates listed in row 1:
=IF(TODAY()=B1,HLOOKUP(....,....,....,....),"")
--
John C


"Txlonghorn76" wrote:

All of the contents of the cells are Hlookup formulas that lead to a main
data sheet. Once the main data sheet is updated, we open this worksheet & it
will automatically update the cells for yesterdays numbers.

"John C" wrote:

My question is: Where do the values come from as far as number of calls? In
other words, why is there a 0 for Oct 8, 9, and 10? When those dates are
obviously not here yet. If these are manually entered, why are they entered
before the date has come to pass?
--
John C


"Txlonghorn76" wrote:

This is what the sheet currently looks like:

MTD Average 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct
08-Oct 09-Oct 10-Oct
=Average(B2:K2) 1 7 6 0 3 5 0 0 0 0

I hope this makes more sense, I should have posted this from the beginning.
I am trying to have one formual so we don't have to change it everyday. Can I
combine =today()-1 with the average somehow?

"John C" wrote:

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



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default Sum Function that does not include 0

You are welcome, and thanks for the feedback.
--
John C


"Txlonghorn76" wrote:

That worked wonderfully! Thanks!

"John C" wrote:

Then why not have your formulas be like the following in row 2, addressing
the dates listed in row 1:
=IF(TODAY()=B1,HLOOKUP(....,....,....,....),"")
--
John C


"Txlonghorn76" wrote:

All of the contents of the cells are Hlookup formulas that lead to a main
data sheet. Once the main data sheet is updated, we open this worksheet & it
will automatically update the cells for yesterdays numbers.

"John C" wrote:

My question is: Where do the values come from as far as number of calls? In
other words, why is there a 0 for Oct 8, 9, and 10? When those dates are
obviously not here yet. If these are manually entered, why are they entered
before the date has come to pass?
--
John C


"Txlonghorn76" wrote:

This is what the sheet currently looks like:

MTD Average 01-Oct 02-Oct 03-Oct 04-Oct 05-Oct 06-Oct 07-Oct
08-Oct 09-Oct 10-Oct
=Average(B2:K2) 1 7 6 0 3 5 0 0 0 0

I hope this makes more sense, I should have posted this from the beginning.
I am trying to have one formual so we don't have to change it everyday. Can I
combine =today()-1 with the average somehow?

"John C" wrote:

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I include both AND and OR in the same function? Dave Excel Worksheet Functions 7 September 24th 08 11:44 PM
Date Function to include a day and automatically updated each new Kula Excel Worksheet Functions 1 May 1st 08 06:48 AM
how do I include advance payments in the PMT function Razzle Dazzle Excel Worksheet Functions 1 November 3rd 05 08:10 PM
Can SUMIF function include AND function ShaneS Excel Worksheet Functions 1 May 17th 05 03:24 AM
Include Saturday in the WORKDAY function kippi3000 Excel Worksheet Functions 9 December 31st 04 09:21 AM


All times are GMT +1. The time now is 11:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"