Average of all numbers if...

[vexum]

Hey!

I'm having a bit of a problem with explaining what I want. So I have two columns. In one are progressively increasing numbers in succession, but some of them may repeat, because I have rounded them up. So what I want to do is, to average values for example for 351, and have them in one cell, so its easier to make a graph. So instead of 3 values for 351, I'll have one that averages them. Can that be done and how?
Capture.JPG
Thanks

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[vexum]

Aaaa okey just saw your guide... Sorry for before :D

[Norie]

If you named the 2 columns of data you could create a pivot table that would list all the unique values from the first column with the average for the correspoding values from the 2nd column.

You could then use the data in the pivot table to create a 'standard' chart or create a pivot chart.

In the attached file I've opted for the former.

PS I used Field1 and Field2 because I'm not sure what the data represents, I'm sure you can come up with something more descriptive.

[vexum]

I'll play with this table a bit, thanks. Basically, A column is length of light, and B column tells you how much of that light has been reflected at that length. That's why I'm trying to merge all the same numbers since its basically same frequency.

[Richard Buttrey]

Hi,

Your column A numbers also have decimals whereas it appears you want averages for Integer values. In which case you'll need to first round your numbers
e.g. in C14 enter =ROUND(A14,0) and copy down.

Then in E14 copied down you could use

Please Login or Register  to view this content.

Or as Norie has suggested use a Pivot Table once you've rounded your basic numbers.

[Norie]

Oops, didn't spot those pesky decimals.

[vexum]

Thanks for that last tip... I have just one last question, how do i rename all the damn row names :///// Capture.JPG

A BIG THANK YOU TO BOTH OF YOU GUYS REALLY!

[Richard Buttrey]

Oops, didn't spot those pesky decimals.

....Me neither at first and it took several minutes to work out why COUNTIFS() wasn't working....

[Richard Buttrey]

...Hi,

I'm not sure what you're getting at. There are no columns with Row labels like '351-351'.

And why in any case would you want such labels when it seems you're wanting to average numbers based on a set of discrete integer values?

[Norie]

Richard

I think the OP might have grouped the data from the first column in the pivot table.

I tried to round the values in the first column via the Pivot Table options but couldn't find anything that worked, so I think the only solution is a helper column using the ROUND formula you posted earlier.

[vexum]

Forget about it i was just being stupid *facepalm*

Thank you for your help again. Reped both :D

[Richard Buttrey]

Does the attached help.

I've used Norie's Pivot Table approach.