I have a question about averageing the cells in column A that correlate with the cells in column B that have the exact same value.
In other words I want to average all of the cells in column A that have a value of X in column B and then do it again for all the cells that have a value of Y.
Can anyone help me with this?
Try:
=Averagif(B:B,"X",A:A)
and
=Averageif(B:B,"Y",A:A)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I could do that, but there are about 800 different values that I would have to do that for.
What I would really like to do is tell excel to average all values in column A that correspond to values in column B that are exact.
I know about the Average formulas, the average if or and formulas, and the exact formulas.
Is there anyway to tell it to AVERAGE IF value is EXACT?
I am not sure what you mean by EXACT.. do you mean there are numbers in both columns A and B and you only want to average numbers in A if they are equal to the numbers in Column B.
e.g if there is 2 in A5 and a 2 in B5, then include the 2 in average formula?
Array formula: =AVERAGE(IF(EXACT(B:B, "X"), A:A))
Entia non sunt multiplicanda sine necessitate
Originally Posted by shg
?? Not sure what this is doing?
No, here is an example;
Cells A1-A5 all have the number 100 in them
Cells A6-A10 all have the number 200 in them
and so on and so on.
Cells B1-B5 have completely different numbers in them
(IE B1 = 465 B2 = 192 B3 = 194 B4 = 47 B5 = 983)
I want the averages of all the values in column B that have the value 100 in the A column and then the averages in column B that have the value 200 in column A.
I do not want to do the averageif formula and type "100" in because I would have to repeat that 800 times.
Why would you have to repeat it 800 times if there's only two numbers (100, 200) to check?
Why can't you list the numbers you want to use as criteria in say X1:X800 and then in Y1 use formula
=Averagif(A:A,X1,B:B)
and copy down... this will use the reference in column X as criteria.
... or have I misunderstood still?
I used "100" and "200" as examples.
These numbers would contine on to 800 or so different numbers. Is there a way to use =AVERAGE(IF(EXACT(B:B, "X"), A:A)) this formula without having to enter a variable of "X" and ask it to simply calculate averages for column A that have values in column B when the values in column B are the exact same each time.
What exactly does the "X1" represent in this formula?
X1 is cell X1.
So if you put 100 in X1 and 200 in X2 and so on down X column.. and then you put formula I gave in Y1 and copy it down you will get averages for values that are in column X
Here is an example: Scroll all the way down to see the second column.
Column B
2010
2010
2010
2020
2020
2020
2030
2030
2030
2040
4040
4040
4040
5050
9995 . . . and so on and so on
Column A (these are the numbers that I need to average)
438
376
35
49
58
97
456
325
3232
232
541
531
5214
5465
Ok understand the X1, but what is the reference to Y1?
Y1 is where you insert my formula. Then copy it, select the rest of the range from Y2 downwards... and paste. The formula will replicate...
You can put those elsewhere if you want.
Take a look at the attached.. I put the criteria in column E and Formulas in column F.. you can put where you want.
wmk0005,
I don't think you are making it easy for anyone to understand.
Help yourself and,
Attach a workbook with sample data and expected results.
modytrane.
It looks like I will still have to enter the values in B manually. Is there a way to automate this with the EXACT formula?
Something similar to =AVERAGE(IF(EXACT(B:B, "X"), A:A)), but without having to enter in the "X" variable every time that it changes.
Maybe you do need to post a sample worksheet and show us what you expect as results....
Here is an example.
I hope that this makes more sense.
I just posted it with two sample averages.
In the example you can see when and where I need the averages. I simply used a basic average formula with a range of cells in it.
I want to be able to use a formula that I type in once though and copy all the way down the column.
Ok... try:
=IF(B3=B2,"",AVERAGE($A$2:A2)) copied down...
See attached...
In D2: =SMALL($B$2:$B$1377, COUNTIF($B$2:$B$1377, "<=" & D1) + 1)
In E2: =AVERAGEIF($B$2:$B$1377, D2, $A$2:$A$1377)
Copy down.
My previous post formula gives results you displayed... but not sure that is really what you wanted...
I think you only want to average for the same numbers in column B...
Then try:
=IF(B3=B2,"",AVERAGE(IF($B$2:B2=B2,$A$2:A2)))
or if you are using XL2007
=IF(B3=B2,"",AVERAGEIF($B$2:B2,B2,$A$2:A2))
see new attached.. for 2003 version.
shg,
I think both of us answering the same question differently and continuously might be confusing the thread... I will sign out of this one now as I don't want to cause confusion...
wmk0005,
see the attached file.
The answer you are looking for is in column D.
Column E is a helper column.
You can hide it if you like.
This approach works only if you data is organized as in your file.
If your data is going to be mixed, in other words not consecutive in column B, then you would need more complex formulas.
hope it helps,
modytrane
I figured out that if the values in column B are mixed up, it's not very difficult to calculate average for each unique value in column B.
Look at the attached file. First Column G calculates all unique values from column B. Then column H calculates average of corresponding values from column A.
Hope you can use one of these solutions.
modytrane
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks