IF Formular with Average Formular

[Ginger2k9]

Hi Guys,
I have tried for ages to get this to work but it just doesn't want to

what i have is the following

A B C D E F G H
1 1 1 1 4 5 6
2 1 1 2 4 5 6
3 1 2 2 4 5 6
4 2 2 2 4 5 6

What i need to do is work out the average of cells E, F and G if cells A,B and C have 1 in them, if it has the number 2 i want this to not add it nor average this so it completely ignores any column with 2 in it and doesn't add that cell to the amount the finial number needs to be divided by,
For line 1 it would do the following 4 + 5 + 6 = 15 / 3 = 5
For line 2 it would do the following 4 + 5 = 9 / 2 = 4.5
For line 3 it would do the following 4 = 4 / 1 = 4
For line 4 it would do the following 0 = 0 / 0 = 0

but here is the problem, i have around 900 lines of this data and cannot go adding it all up as its not all the same numbers, and most have decimal places too, and even worst then that, they are not next to each other, there is about 20 spaces between A, B and C and same for E, F, G i have just simplified it here to explain what i see and how i need to do this.

I cannot move the cells to put them next to each other as this is how the report runs and data gets lost and mixed up, i have tried one simple formula to many 'IF' statements.

What i think i need to do is do 3 if statements?

[ChemistB]

Try this in H copied down

=IF(COUNTIF($A1:$C1,1)=0,0,SUM($E1:INDEX($E1:$G1,COUNTIF($A1:$C1,1)))/COUNTIF($A1:$C1,1))

[Ginger2k9]

Hi,
Many Thanks for that, it works great when i add it into my simplified spreadsheet, however when i put it into my main spreadsheet i cannot work out how to select only certain cells, for instance i need it to look at column n, q, u if it has 1 or 2 in and add up the data from X, AC, AF

Thanks

Ginge

[ChemistB]

Hmmmm, COUNTIF won't work on a non-contiguous range. Can you upload an example of your sheet showing how it's set up with headers and such? (Go Advanced>Manage Attachments). Maybe we can use SUMPRODUCT or another workaround

[Ginger2k9]

Hi,
i have now sorted it by making a spreadsheet i copy the report into onto sheet 2 and on sheet 1 all the data is in order and next to each other by doing a simple *=(column) formula in every cell with your formula next to the three columns i need to work out giving me the answers.

Many thanks for your help, i have gave you great reputation for helping out.

i just now need to figure out how to close this post down o.o

[Ginger2k9]

This Problem Is Now SOLVED!!! Many Thanks To ChemistB!!

[XOR LX]

@ChemistB

Does that beat your record for Biggest Thank You?

[ChemistB]

LOL, it does!
Thanks Ginge

[Ginger2k9]

i now need help with the next part of the spreadsheet, i need to search F3, G3, H3, I3, J3, K3, L3, M3, N3, O3 for values that total 6 figures and then return that figure, even though there is 9 columns only one will have 6 characters in it.

Maby a left lookup?

[ChemistB]

Try
=LOOKUP(2,1/(LEN(F3:O3)=6),F3:O3)

[Ginger2k9]

Works perfect with the old report, not so great with the new report, it now has to battle between two options that have 6 characters and its choosing the last option as its choice, i need it to choose the first 6 character data it comes across so for example, there is one in G3 and one in J3, but its picking up J3 as its choice :/

i think im about to give up and just do everything manual but its gonna take me forever, please tell me there is an easier way?? please!!!

[ChemistB]

This is an array formula. Instead of hitting ENTER to enter it, you need to hit SHFT CNTRL ENTER. If done properly, you'll see {} appear around the formula

=INDEX(F3:O3, MATCH(TRUE, LEN(F3:O3)=6,0))
Does that work for you?

[Ginger2k9]

IT WORKS!!!!!

Thank You Again ChemistB!!!!

This Is Now Solved Finally!!!