Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank
cells, dashes and < entries. Is it possible to average only the numbers
ensuring that the divsor is the number of cells that contain number entreis
rather than all cells containing an entry (eg for below example answer should
On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:
>I have a column of data as illustrated below than contains numbers, blank
>cells, dashes and < entries. Is it possible to average only ...averaging less than values
How do I average a column of numbers where less than values are calculated as
In the average I want the<2 to = 2. So the answer should be 3
Assuming source data as posted in A2:A7
Paste this in say, B2,
then press CTRL+SHIFT+ENTER to array-enter the formula:
Adapt the ranges to suit
Downloads:17,500 Files:358 Subscribers:55
> How do I average ...Average of Averages
Another seemingly simple process which I can't figure. :)
I have a list of averages, and I want to get the average of them.
I need a formula to average something like the above.
=AVERAGE(A1:A4) is one possible solution.
Hope it helps.
goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838
View this thread: http://www.excelforum.com/showthread.php?threadid=513937
It really depen...Averages
I have an interesting situation. I basically inherited an older
database that is used to enter dates, usage and charges for accounts.
The situation is that we are attempting to do averages filtered by
account, and averaged against the number of days that data has been
entered. So, for example, we had 7 months of data entered, our query
should total the number of days in the seven months (x), then average
that with the total usage number (y). This data currently resides in
the same table labeled "data" in the form of from and to dates
(6/1/1999 to 7/1/1999) and usage (32...Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts
weekends, or only business days?
The average lead time is calcualted as follows: Enter the average number of
days that pass between the time you place an order with this vendor and the
time you receive the order. If you're using Purchase Order Processing, this
field will be updated each time a shipment or shipment/invoice is posted in
Purchase Order Processing. The average lead time is calculated using the
following formula: [(Number of Receipts) * (Average Lead Time) + (Received
Date - Ordered Date)] / (...Average Function
I have a formula in a cell that takes the average Sales of
the 12 rows (for Jan to Dec)
like so: =Average(A1:A12)The problem is that for example,
since therea are no sales figures from August to Dec, the
average is underestimated because it averages it over the
entire range of cells (though they are zero from Augus to
How can I make this Average function to be smarter and
only average those months that the sales is not zero
(August to Dec is Not BLANK but its zero)
Average is a very simple thing: SUM/COUNT. So
should do for all years with 12 mont...averages
Trying to work out the best way to get an average answer based on a
set of answers
The answer will be 1 - 10 across a number of questions
B C D E F
Answer Q1 Q2 Q3 Q4
1 1 1 3 3
2 1 0 1 2
3 0 1 3 3
4 1 0 1 1
5 1 1 6 7
6 2 2 5 2
7 2 3 3 4
8 2 1 7 6
9 1 2 1 3
10 2 2 8 7
Total 13 13 38 38
Average ? ? ? ?
What is the best way to get the average response to each question.
i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure
this is right....Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly.
I have a spreadhseet:
How can i calculate the average of every month between 1999 and 2005?
In other words, i need something which looks up all the weeks in a
particular month (ie Jan 2001) and averages them out into a table:
1999 2000 ......... 2005
Jan 2.45 6.42
Feb 3.64 etc etc.
I was thinking vlookup, but don't know how to average in a vlookup.
I would ...Moving Average
Is there a formula for calculating the moving average for a group of numbers
I'm assuming that you want a running average (at least, that's how I am
Assuming your values are in column A and your average in column B
the formula in B1 should be as follows:
then just copy and paste it down the rest of the column and it will
calculate the average as numbers are added.
> I'm assuming that you want a running average (at least, that's how I am
> interpreting it)
I think the OP want...Averaging?
This must be easy to do but I cannot figure it out...
I have 150 rows of data with information in cell A, B, C and D. Cell A
is simply a description for each row and cells B, C, and D contain
numeric values between 1 and 100. I need to average each row to its
respective cell E and then sort row A from highest to lowest based on
the average value in row E.
A B C D E
Song1 52 45 25
Song2 33 55 88
Song3 44 94 22
Copy down to E150.
Select E1. Choos...Running Average or YTD average
Is there a simple formula for calculating a running average?
I can do accomplish what I want to do but it takes 4 different steps or
functions to get there.
Example of what I would like:
A-1 thru A-52 equal weeks of the year.
We'll say that A-5 represents the 5th week of the year.
Income for these weeks will be entered in it's respective cell as it
In the cells that represent the weeks that have not yet come, a "0" is
I want to be able to have one cell that reflects the year todate
average of income, and not be affected by the zeros.
-------...How to obtain Averages from a list of multiple items?
Example:Excel worksheet-Column A has list of Cat or Dog (say 15 items)-Column
C has their age in days old (10,3,4,etc)
I need (2) Averages- Average age of Cats and Average Age of Dogs
Do the same for dog.
"MadameJunk" <MadameJunk@discussions.microsoft.com> wrote in message
> Example:Excel worksheet-Column A has list of Cat or Dog (say 15
> C has their age in days old (10,3,4,etc)
> I need (2) Averages- Aver...Dynamic annual average
Hi, I keep a rough weekly diary on the development of a certain numeric
value. Now I've got a series of data entries in a chart as follows:
A1 - date of entry, eg. "23rd July 2006"
A2 - reading data value, eg "100"
I want to create a dynamic average on line A3 which tells me the
average of data entries during the past year. Problem is, I've read the
data value in irregular intervals and not every day (last year for
example on 22nd July, the year before 10th July etc). My Excel
understands the dates entered and draws a neat proportioned chart on
development of data...Average Sale #2
I want to see if a recent spend �15 and receive free box of chocolates made
a difference to the average sale.
How can I see the average sale by day on a report?
the simplest way to do this is to point Excel at your database and drag the
information out - use the built in Excel features to summarize your sales by
date - The TRANSACTION table is the one to use.
"chris allsopp" wrote:
> I want to see if a recent spend £15 and receive free box of chocolates made
> a difference to the average sale.
> How can I see the average sale by day on a report?
...averaging class grades
All I could get it to do was average 3 weeks grades instead of the 6 weeks I
put in for (from G1:M1) and by highlighting the whole area-I used the
=AVERAGE,etc. I'm not sure what I did wrong still.
Please stay with one thread.
Let's say you have the data like this
John 6 8 9 5 6 7
Mary 6 8 3 7 8 9
Zak 5 6 7 8 4 7
And that John's name is in A2 and his marks are in B2:G7 and Zak's in
The average of John's marks is found with =AVERAGE(B2:G7)
The average of week 1 marks with =AVERAGE(B2:B22)
Bernard V Liengme
Microsoft Excel MVP
I'm averaging 52 rows which are week totals. I have a result column for the
average. Problem: If I have only say 30 of the 52 weeks currently to
average, the formula that does my week totals has a zero in the week rows
that haven't happened yet, so my running average is way too low due to the
zeros. Is there a way to include an if condition for the averaging, like
include this row IF the value is greater than zero?
Or, is there some simpler way to solve this? I could of course not include
the totals formulas until that week has data, which would give me blank
cells for unu...Averaging in arrays
I thought I saw the answer to this before, but I can't seemt to find it
Here's the scenario
Column A has lists of Cities (ie: Boston, Chicago etc..)
Column B has List of inventory
Column C has amout of time in hours:Min:sec sitting in inventory
Column lengths could be in hundreds
I want to do formula that Produce results for example
Boston Blue widgets 236:45:00
All help appreciated
Array-entered, meaning press ctrl/shift/enter.
>-----Ori...Why can't you average an average ?
What's the formal or definitive explanation for why you can't average
dataset of averages ?
I understand about the skew effect if no account is taken of th
original sample sizes for the averages to be averaged, but what if the
are all the same size ? Would it then be valid to average them ?
Blewyn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=279
View this thread: http://www.excelforum.com/showthread.php?threadid=27751
Example: The average of 2...2 part question on averaging
I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
job averaging the data as long as there is a numeric value in one of the
cells. However, if there are no numbers I get a div/o error. Is there a fix
Question 2; Is there a way to average say the 5 highest values in the
"Geo" <Geo@discussions.microsoft.com> wrote in message
I need the average calculate, but all the zero cell I need delete, to
correct calculate. It exists some formula if excluding the zero cells ?
Sorry, I�m brazilian and my english is very bad !
entered with ctrl + shift & enter
(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
"Luis Felipe Alcantara" <firstname.lastname@example.org> wrote in message
> I need the av...Compound Average Growth Rate
Does anyone have any ideas about a formula for CUMULATIVE Compound
Average Growth Rate (CAGR)
for budgeting purposes???
Message posted from http://www.ExcelForum.com/
Do we assume you have a bunch of individual line items, which have a growth
rate, for which you are calculating CAGR using the RATE function? Then you
want to sum the line items, and calculate the CAGR for the total line?
If so, calculate CAGR on the total line the same way you do it for the line
items -- feed RATE your term (likely the same as the line item), PV (sum of
line item PVs) and FV (sum of line item FVs).
It ...Weighed Average of a weiged average when there are blanks
This might be a bit complicated, at least for me it is
I need to calculate a total weighed average of a few weighed averages
so i have few columns of credits
then I have columns of grades between 0 and 5
in some course I havent gotten grades yet so there are blanks
I can get the weighed average of corresponding columns with this kind
=SUMPRODUCT(C1:C5;D1:D5)/SUMIF(D1:D5;">0";C1:C...Time Averages/ Time of Day and Minutes
I am working on a database that tracks dispatch time/ arrival time/ and how
many minutes to scene. How do I 1: record the time and then query the time to
show an average dispatch time? (I assume the arrival time equation will be
the same) 2: set up a query to calculate the difference in arrival time from
dispatch time, and then the subsequent average response time?
On Mon, 28 Jan 2008 16:04:02 -0800, Scotty <Scotty@discussions.microsoft.com>
>I am working on a database that tracks dispatch time/ arrival time/ and how
>many minutes to scene. How do I 1: record the tim...average
i have a summary of sales statement like jan 5 ,feb 5 ,mar 5 , apr 5, jun 5
, jul 5, aug 5 , sep 5, oct 5 , nov 5 , dec 5 and after that we do total for
all 12 months 60
so now we find average
like this =average(.........) all numbers
but if we dont have a sell in jan month n we dont have a sell in feb month
so remaining 10 months so shown me in total only 10 months sale not 12
months sale autometically we generate only once time formula either we have a
sell in jan month or either we have a sell in feb months
i also try this formula
or if formula
If I want to obtain the average of values in a column, using only those
cells which have a value in them, which function do I use please?
it ignores cells that are empty and cells that contain text.
> If I want to obtain the average of values in a column, using only those
> cells which have a value in them, which function do I use please?