Microsoft Excel 2003 (full product)

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.

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

HUMAR 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,

ThomasD

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

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

You're very welcome. Regards

Ask Your Question

Weekly Poll

Would you be willing to go on a SpaceX trip around the moon?

Discuss in The Lounge

Poll History