A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

help on averaging function



 
 
Thread Tools Display Modes
  #21  
Old July 18th, 2007, 12:48 AM posted to microsoft.public.excel.worksheet.functions
driller
external usenet poster
 
Posts: 698
Default help on averaging function

for the first time!!g
--
regards,
driller

*****
- dive with Jonathan Seagull



"Dave Thomas" wrote:

hehe, have fun!
"driller" wrote in message
...
thanks all,
outta here!

--
regards,
driller

*****
- dive with Jonathan Seagull



"driller" wrote:

sorry Sir Chip Pearson,

not aware of that,
yet the starter solution i gave seems the same considering that (just
guessing its the positive),
my logic is if 0 is out of the numeric range, then either the positive or
negative range will be a selected criteria
*and not necessarily both of it...* unless deem logical...in any sense.

maybe treesy has to specify now which range of data he/she prefer "the
positive or the negative"

cordially,
--
regards,
driller

*****
- dive with Jonathan Seagull



"Chip Pearson" wrote:

Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than
just ENTER when you first enter the formula and whenever you edit it
later.
If you do this properly, Excel will display the formula enclosed in
curly
braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it
is
only
filled in through June. At the very end, I want it to average some
of the
data. The formula has 12 cells that I want averaged but if one of
those
cells is blank or zero, I want it ignored. 6 of the 12 cells have
data.
Right now, I want it to average the 6 cells that have data. Next
month,
it
will average 7, so on and so forth. Right now, it is taking the 6
values
and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess
I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??




  #22  
Old July 18th, 2007, 12:50 AM posted to microsoft.public.excel.worksheet.functions
Dave Thomas
external usenet poster
 
Posts: 146
Default help on averaging function

To extend your example:

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.

Average Profit (including 0), $60, (excluding 0), $75.

"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.


Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin



  #23  
Old July 18th, 2007, 12:56 AM posted to microsoft.public.excel.worksheet.functions
Meebers
external usenet poster
 
Posts: 102
Default help on averaging function

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late, however
the goal is the average of 0. meaning they are taken in on time. ;0)



"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.


Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin



  #24  
Old July 18th, 2007, 01:02 AM posted to microsoft.public.excel.worksheet.functions
driller
external usenet poster
 
Posts: 698
Default help on averaging function

Dave,
Which one of the similar sample average deems to fit the OP,,timewise...?
just curious really!

--
regards,
driller

*****
- dive with Jonathan Seagull



"Dave Thomas" wrote:

To extend your example:

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.

Average Profit (including 0), $60, (excluding 0), $75.

"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.


Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin




  #25  
Old July 18th, 2007, 01:04 AM posted to microsoft.public.excel.worksheet.functions
driller
external usenet poster
 
Posts: 698
Default help on averaging function

Meebers,
u mean timewise..isnt it? but with 0.
--
regards,
driller

*****
- dive with Jonathan Seagull



"Meebers" wrote:

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late, however
the goal is the average of 0. meaning they are taken in on time. ;0)



"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.


Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin




  #26  
Old July 18th, 2007, 01:10 AM posted to microsoft.public.excel.worksheet.functions
MartinW
external usenet poster
 
Posts: 848
Default help on averaging function

Yeah.
The only formula to work correctly in this situation is a simple
=AVERAGE(A1:A12)
With a caveat that zero values MUST be shown as zero and not blank
and blank values MUST be shown as blank and not zero.

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.
Month 6. ""
Month 7. ""
Month 8. ""
etc.

Regards
Martin


  #27  
Old July 18th, 2007, 01:18 AM posted to microsoft.public.excel.worksheet.functions
Meebers
external usenet poster
 
Posts: 102
Default help on averaging function

The goal is to see the patients on time. If we are late, it is a negative
amount, early it is a positive. Yes the differences are in times, results
are formated as a number since negative #'s will not format as hh:mm.

"driller" wrote in message
...
Meebers,
u mean timewise..isnt it? but with 0.
--
regards,
driller

*****
- dive with Jonathan Seagull



"Meebers" wrote:

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late,
however
the goal is the average of 0. meaning they are taken in on time. ;0)



"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin






  #28  
Old July 18th, 2007, 02:43 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default help on averaging function

"Dave Thomas" wrote...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

....

Name a physical, financial or not entirely esoteric process that could
produce positive and negative values but not zero values. The point is that
if a set of numbers can include positive and negative values, they very
likely could also contain legitimate zero values that shouldn't be ignored.

As for blanks, they're always ignored unless you mean "" and are (mis)using
AVERAGEA instead of AVERAGE or are averaging values through an external
reference to a closed workbook.


  #29  
Old July 18th, 2007, 02:49 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default help on averaging function

"Dave Thomas" wrote...
His data could be all negative numbers for all I know. He said he just
wanted to eliminate 0's and blanks.

....

In that case,

=AVERAGE(IF(dataset0,dataset))

or getting really pedantic,

=CHOOSE(1+SIGN(COUNTIF(dataset,"0"))+2*SIGN(COUNT IF(dataset,"0")),
0+N("no positivess or negatives, so live with 0"),
AVERAGE(IF(dataset0,dataset)),AVERAGE(IF(dataset 0,dataset)),
AVERAGE(dataset)+N("both positives and negatives, so should include 0s"))


  #30  
Old July 18th, 2007, 02:57 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default help on averaging function

"Dave Thomas" wrote...
Actually the average would be 100, but your point is well taken. You can
have a mixture of positive and negative with no zeroes.

....

Actually the example completely misses the point. It'd be necessary to use
the column containing profit or loss in order to change signs. And the
lovely example provides a very nearly meaningless average - cumulative
return divided by number of periods is +100, but 2 out of 3 periods
generated negatives.

Further, there are no zeros. If there are no zeros, then just use AVERAGE.
If, however, there are zeros, e.g.,

Month 1 100 loss
Month 2 100 loss
Month 3 500 profit
Month 4 0 no return
Month 5 0 no return

is the average monthly return still 100 or is it now 60?

This has everything to do with real world processes. Averages of all
negatives, or averages of all positives, or averages of all numbers
(positive, negative AND zero) make sense. Averages of positives and
negatives but not zero never make sense.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:12 PM.


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