Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Randy Lefferts
 
Posts: n/a
Default 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
  #3   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Randy Lefferts
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Randy Lefferts
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Randy Lefferts
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Randy Lefferts
 
Posts: n/a
Default

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
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
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Formulas in source data Ken Charts and Charting in Excel 3 December 1st 04 06:43 PM
Assigning Cells in worksheets to other data in other worksheets. David McRitchie Excel Discussion (Misc queries) 0 November 27th 04 07:15 PM
Selected cells grow and data entry impossible EXT is dissabled Allanhart42 Excel Discussion (Misc queries) 1 November 26th 04 03:59 PM


All times are GMT +1. The time now is 06:02 AM.

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"