If statement

[flickflucker]

Hi I am a novice with excel but need to know if there is a formula I can put in a cell to achieve this.

I need to get an average of the E column when the adjacent A column, B column and C column have cerain values.

I have sort of done it by creating hiden columns and the if statement but as there are 108 different combinations of A B and C I have had to make 108 hidden columns and this isnt ideal.

so for example If A1=x and B1=y and C1=z then E1 would be included in the average and if A2=x and B2=f and C2=z then E2 wouldnt be included in the average etc.

I hope this is clear and I hope you can help.

Cheers Flickflucker

[Leith Ross]

Hello flickflucker,

Welcome to the Forum!

My first question is, will this data be changing or is it static? You gave a couple of examples of the If...Then statement, but are there any mathematical rules controlling the decisions? If there are then an algorithm could constructed to simplify the code. Let me know.

Sincerely,
Leith Ross

[flickflucker]

ok it a little hard to explain but I will try.

Basically I am going to be pasting a table onto excel.
it is 5 columns wide.
A column is one of 9 different possibilities
B column is one of 6 possibilities
C column is one of 3 possibilities.
D column is blank
E column is a numerical number.

A B and C are all words.

on page 2 I want to put 3 tables (one for each of the possibilities of C)

each table will be 9 cells down(each of the possibilities of A) and 6 across(each of the possibilities of B)

Then this bit is where my explination becomes harder to follow.

I want to put a formula in each cell so it looks through the table I pasted on page 1 (which can be any length but allways the same 5 wide)

it needs to find all the rows where the A value B value and C value match the corrisponding cell in the table, and average The E values of those rows.

The table on page 1 will become longer with time but previous values will allways stay the same (just more added to the bottom)

each time I add more to the bottom I would like the values on page 2 to adjust automatically.

I dont know if this can be done but would appreciate any help.

Thanks

Flickflucker

[Leith Ross]

Hello flickflucker,

If you can post your workbook, it would help me follow what you are doing, and also provide you with some solutions. The Forum only allows workbooks that are zipped to be attached to posts.

Thanks,
Leith Ross