Finding An Average Couple Numbers
Jul 9, 2008
I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.
I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.
View 11 Replies
ADVERTISEMENT
Aug 25, 2014
Please find the attached MS Excel 2010 file <average set.xlsx>.
There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.
Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.
The Column H and Column I also same as like above , but here negative set of numbers starts first.
View 4 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
Feb 25, 2014
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333
2 Sam 22
3 Sam 22
4 John 5
5 Sam 22
6 Sam 1
7 Tom 3
8 Tom 333
So the answer would be
Tom = 2
Sam = 2
John = 1
View 8 Replies
View Related
Mar 24, 2007
Sub maxTemperature()
Sheet6.Select
Dim myrange As Range
Range("b1").Select
myrange = ActiveCell. CurrentRegion
cellcount = myrange.Count
imax = ActiveCell.Value
i = 2
Do
Do While i < cellcount
If Cells(i, 2) > imax Then imax = Cells = Cells(i, 2).Value
i = i + 1
Loop
Range("a25").Value = imax
isum = 0
For i = 1 To cellcount
isum = isum + Cells(i, 2)
Next cellcount
ave = isum / cellcount
Range("a26").Value = ave
End Sub
View 8 Replies
View Related
Mar 21, 2008
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.
If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
View 9 Replies
View Related
Mar 10, 2009
I have 2 rows of data
1. title
2. value
eg
Row 1: Title1, Title2, Title1, Title4, Title2, Title1
Row 2: 2 , 3 , 0 , 1 , 2 , 6
In the above I am looking for the average of Title1, where the value is greater than 0. There are 3 occurances of Title1, but only 2 have values. The answer i am after is Sum is 8, average is 4 (ie 8/2 and not 8/3)
View 3 Replies
View Related
Nov 20, 2009
I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.
View 7 Replies
View Related
Jan 2, 2009
I’m using the following formula:
SUMPRODUCT(--(Issue="Yes"),--(Month=Aug),--(Building=$A$9))
To find the number of instances where there was an issue in a certain building during a certain month.
What I can’t figure out is how to find the Average number of such instances for the past 3 months, the last 6 months & the trailing twelve months. If I change the formula to:
SUMPRODUCT(--(Issue="Yes"),--(Month>April),--(Building=$A$9))
I get the correct sum for the months of May, June, July & Aug but I need an average number of instances per month for the last three, last 6 and TTM but not to include the current month.
View 9 Replies
View Related
Jul 30, 2014
I have a number of rankings, all from different spreadsheets that I wish to combine and average out.
Thus far I have pulled all the relevant worksheets into 1 spreadsheet. The issue I'm having is that each worksheet contains slightly different names.
So as an example, Sheet1 may have 4 columns: Bob Hope | 4 | SE | PM |
Sheet 2 may have 2 columns: | Bob Hope, PM, SE | 8 |
Sheet 3 may have 3 columns: | Bob Hope, SE | PM | 9 |
What I need to do is have a master sheet which just pulls through the name and then the average of the scores, i.e.: | Bob Hope | 7 |
I have approximately 400 people that I need to do this with.
View 3 Replies
View Related
Jun 1, 2009
I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average dosen't contains all integers
eg if range is A1:A10
then data is like
79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A
now ave for this range can't be calculated directly as many values are strings
what i need is
using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value
here's da avg dunction which i had used in macro
View 6 Replies
View Related
Oct 18, 2005
I'm trying to find the average for a person with scores in column 4 on 3 separate sheets. The following function returns #VALUE.. Because the person may be in a different row on each sheet, I'm thinking I need to use the VLOOKUP function.... Also, as an aside, the person may have a zero on one sheet that would affect the average score...
=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))
View 14 Replies
View Related
Feb 27, 2013
I have to find the average of a set of data with a lot of different criteria. I think an array formula would work wonderfully but I just cannot seem to get it.
My example: I have a large set of data that I must get multiple averages from that fall within differing sets of criteria. For my example we'll just say that I have to find the average of the number column that occurs on or after Jan-12 (January 2012) and is type "p1" or one that has no date and is type "p1" and to not average numbers that are equal to 0. So logically it would look like this: If date
View 2 Replies
View Related
Jul 20, 2013
Finding an average age from a list of dates. This is just a small sample:
7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013
I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.
View 9 Replies
View Related
Jul 26, 2012
I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:
KEY:
MS = Class CO = Company PLT = Platoon SQD = Squad
A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)
A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)
F14 is the average of all PT Scores
A
B
C
D
E
F
1
NAME
MS
CO
PLT
SQD
PT SCORE
[Code] ....
I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.
A
B
C
D
E
F
G
H
I
J
K
1
MS 1
MS 2
A
A/1
A/2
A/1/1
A/1/2
A/1/3
A/2/1
A/2/2
A/2/3
[Code] .......
View 2 Replies
View Related
Mar 11, 2014
I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.
I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.
For example, if the HC per month would be:
May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5
in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?
Month
Type
L/H
Prod type
HC
[Code].....
View 9 Replies
View Related
Apr 24, 2014
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
View 2 Replies
View Related
Aug 4, 2009
I have 5 years of data in 2 columns:
Col A. Col B.
8/2/2004 Value 1
to
7/31/2009 Value n
I have a table set up as follows
1 2 3 4 5 6 7 8 9 10 11 12
2004
2005
2006
2007
2008
2009
I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...
I tried the following to no avail... I'm just getting a zero value:
=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))
View 9 Replies
View Related
Dec 24, 2012
I am currently using Excel 2003. I have a worksheet with two tabs.
First tab has a list of bank Names. Second list has Bank Names and balances.
I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.
View 6 Replies
View Related
Jan 27, 2012
I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
View 9 Replies
View Related
Apr 16, 2007
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies
View Related
Dec 1, 2009
I would like to write part of the code that select a couple of cells in a row (for example : Range("A1:A10).select) than:
-find a text which is in one of these cells (for example "HP") - and gives me back column in which this text is input
texts in selected cells will always be the same (for example : HP, CH, SD, .... ect.)
View 9 Replies
View Related
Mar 15, 2003
I am trying to create a new workbook out of an existing one by exporting three sheets. I need to bring along a couple of userforms.
View 9 Replies
View Related
Nov 16, 2012
Is there a function in excel that can look at an array of values and find the two values that equal a number.
I have a number 159. I have an array of 100 numbers but only two of them added together can equal 159. Rather than sort through every possible combination is there a function in excel that can do that?
View 2 Replies
View Related
Jul 7, 2014
I want to create 4 quarter sheets at the same time, but my code could only process 1 quarter sheet each time. Also, I have trouble of copying and pasting the values from yearsheet to the quarter sheets. ( see attachment document )
View 5 Replies
View Related
Dec 10, 2009
to create a macro that counts a number of values for a couple of types. The list looks like this;
29 bananas
13 apples
18 bananas
14 pears
7 pears
etc.
So i want to create a loop the goes through the list and adds up all bananas, apples and pears. How do I do that in the best way?
View 9 Replies
View Related
Dec 30, 2008
So I have multiple columns of numerical data,
I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.
How do I do that?
Is it SUMIF? I cannt seem to grasp this.
View 11 Replies
View Related
Mar 29, 2009
I have another post here on this forum, but I'm afaid the formula is getting so complex that nobody is able to fully understand what I want. Instead I want to find a value between two numbers and add it to some IF sentences. It will do what I want, even if it's not that elegant.
I've looked at the SUMIF function but it did not do exactly what I wanted. It finds a number or adds numbers only if they are in range it seems.
What I want is the following:
Return sum between 500 and 1000 in one cell.
View 11 Replies
View Related
Jan 14, 2013
Create a form where i would enter 2 numbers into 2 seperate boxes, and those numbers would be highlighted/bolded/shown in red from a list of 30 rows of numbers x 20 numbers in each row. these 2 numbers will not be the same all the time, below is just for an example
ie. number: ... (5) number: ... (59)
1 28 19 20 5 9 80 72 12 38 50 53 59 83 ...
8 4 38 29 11 39 27 38 3 49 59 80 13 12 49 4 5 ...
and so on....
....
So picture something like that, but with 30 rows of numbers x 20 numbers in each row.
View 14 Replies
View Related
Nov 10, 2009
I have a large list of numbers in two columns. I need to know what numbers are in column A but don't appear in column B. For example if the numbers are 1 2 and 3 in column A but column B only has 1 and 2 I need to know that 3 is missing.
the only thing I can think of is doing =IF(A1=B1,"TRUE","FALSE") however typing this a thousand times would not be practical.
Is there a macro that can check column A against column B and tell me which ones are missing?
View 10 Replies
View Related
