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 % openingI 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

#1
September 4, 2010 at 09:15:00
 Hi,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:=SUMPRODUCT((\$A\$2:\$A\$101>=D1)*(\$A\$2:\$A\$101<=D2)*(\$B\$2:\$B\$101))/SUMPRODUCT((\$A\$2:\$A\$101>=D1)*(\$A\$2:\$A\$101<=D2))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.Regards

Report •

#2
September 6, 2010 at 11:14:53
 HUMARYes 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,ThomasD

Report •

#3
September 6, 2010 at 12:12:01
 Hi,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:=SUMPRODUCT((\$A\$2:\$A\$101>=D1)*(\$A\$2:\$A\$101<=D2)*(\$B\$2:\$B\$101<>0)*(\$B\$2:\$B\$101))/SUMPRODUCT((\$A\$2:\$A\$101>=D1)*(\$B\$2:\$B\$101<>0)*(\$A\$2:\$A\$101<=D2))Regards

Report •

Related Solutions

#4
September 6, 2010 at 12:25:39
 Humar,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!ThomasD

Report •

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

Report •