Excel formula question, involving averages...?

Question

okay, one one worksheet, I have many columns but three are important:
A=percent of an effect (number)
B=variable (number)
C=type of product (text)
I would like to look at each product and separate the percents (column A) into categories and then take the data from column B and average only that data within a certain percent range and for a certain product. I would like to get one number, the average, in return. I would like to insert this averaged data onto another worksheet in the workbook. How would I do that, without having to separate the first worksheet into many (I have soo much data on that page it would take days to separate).
What should I do?

Answer 1

I'd have to set up a mock-up to really follow what you are saying, but it is possible you could use a formula with greater than/less than to separate the first column into categories, or perhaps insert a few blank lines and do sub-totals (OK, sub-averages). You don't really have to separate them into different worksheets to separate out for sub-calculations, just insert blank lines and put a formula for the average in the blank area. I'd use three blank lines and put the formula in the middle one, to make it easy to read. Then on that separate sheet, use the averages you got in the sub-groups by referring to the cell where the average is in creating your formulas there.

Have I muddled this further, or given you a clue you can use?

Answer 2

Hi umaryland_steph

There is an excellent formula, sumproduct(), that you can use to sumarize your data. You just need to insert other formulas that convert your info in boolean values (true/false).

I also suggest that you use dynamic names in your database in order to let your ranges grow automativcally as you enter new information

Send me an email with a shot example of your data to show you the solution, and then post that answer in this forum

[e-mail]

Answer 3

in case you install diverse quantities at diverse circumstances, you should use the XIRR formulation. In column A, positioned the dates you install the money and at present's date. In column B, positioned the deposits (as negatives), and at present's value as a large. In yet another cellular, positioned =XIRR(ColA, ColB)

Answer 4

Check "Auto Filter" (Data --- Filter --- Auto Filter)

Then check the function "Subtotal"

You should be able to manage with these