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.
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.
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
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
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.
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)
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:
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.
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
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...
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
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
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?
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.
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.
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.)
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?
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 )
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.
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
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?
