Data extraction from 2 Data Columns

Microsoft Excel 2003 (full product)
September 3, 2010 at 16:53:20
Specs: Windows XP, 4 gig
I have two columns:

Column A has values from 0 - 100 representing % valve opening.
Column B has corresponding values reprersenting steam pressure at that % opening

I want to extract the average steam pressures for the ranges 0-10%, 10%-30%, 30%-50%, 50%-70%, 70%-90%, 90%-100% I don't want to have to make the six extra columns to sort data - I have ~ 15 differ tables of % valve open and corrersponding steam pressures. I would to have column for % range and column for Avg Pressure in that % range. I have tried all my tricks and cannot figure this one out - ANY help would be appreciated.

See More: Data extraction from 2 Data Columns

Report •

September 4, 2010 at 09:15:00

For this example I had 100 percent values in column A from cell A2 to cell A101.
I had steam pressure values in the corresponding cells in column B.

Next I created a table of percentage ranges:
(Note you probably don't want overlapping ranges - 0->10 and 10->30. i.e. 10 should not appear in both ranges, so either use 0->10 and 10.1->30 (or similar) or change the proposed formulas to use > instead of >= )

Table in cells D1 to I2:

	D	E	F	G	H	I
1	0	10	30	50	70	90
2	9.9	29.9	49.9	69.9	89.9	100

In cell D3 enter this formula:
Now drag the formula to extend it to cell I3.

You will have average pressure values for each range.

The first SUMPRODUCT totals the pressure values in column B for all rows in the specified percentage range in column A and the second SUMPRODUCT counts the total number of rows within the specified percentage range.


Report •

September 6, 2010 at 11:14:53

Yes that works very well however, I neglected to mention one thing; the value of 0 should not be calculated into the average. Any suggestions? This is where I have been running into trouble.

FYI: for my ranges, I used <= for my upper limit and > for my lower limit. >0 - <=10, >10 - <=30 etc. In this way, I don't have to mess with the decimals.

Thank you,


Report •

September 6, 2010 at 12:12:01

Add a further test to both the total part and the count part of the formula to exclude zero values in column B.

In D3 try this:


Report •

Related Solutions

September 6, 2010 at 12:25:39

Awesome - I owe you big time! I have not used the sumproduct before and I can see how well it can be used. I ran into a divide by 0 error and eliminated that by starting with an if statement using the sumproduct array = 0.

Thanks again - I really appreciate it!


Report •

September 6, 2010 at 13:56:17
You're very welcome.


Report •

Ask Question