IF/COUNT/AVERAGE... Value returned is incorrect.

[Rebekah]

This formula appears to work with some columns of data yet when I change the
column field it enters the vale "o" when there is data to average...

I know this may sound crazy to some, but I am finding this really
complicated to solve.
=IF(COUNT((AVERAGE(IF('Summary Days'!$D$3:$D$1500="2",(IF('Summary
Days'!$K$3:$K$1500=37,(IF('Summary Days'!$P$6:$P$1500<>0,'Summary
Days'!$P$3:$P$1500)))))))*ISNUMBER('Summary
Days'!$P$6:$P$1500)),(AVERAGE(IF('Summary Days'!$D$3:$D$1500="2",(IF('Summary
Days'!$K$3:$K$1500=37,(IF('Summary Days'!$P$6:$P$1500<>0,'Summary
Days'!$P$3:$P$1500))))))),"0")

I know it isn't the tidiest formula... The columns I am looking at read
something similar to:

A B C
Priority Week Time Lapse
2 37 0.2
2a 37 0.0
2 38 0.6
3 37 3.2
2 39 0.0
2 37 0.0
etc

The Time Lapse column is calculated from other columns but the formula I
need is to find an average of the time lapse (for example, column C) if the
priority is 2 and the week is 37.

I have 1500 lines of data and a mix of all priority and weeks.

I also need the formula to populate the cell with a zero value if there
isn't any data above 0.0 (I found that other formulas were returning the
value DIV#/1!)

Please can someone help me find out where I am going wrong and explain it to
me so I can learn from my mistake?!
Many many thanks

 

[Roger Govier]

Hi Rebekah

One way
=SUMPRODUCT(--(A2:A7=2),--(B2:B7=37),--(C2:C7))/
SUMPRODUCT(--(A2:A7=2),--(B2:B7=37))

Change the ranges to suit your data . Ensure they are of equal dimensions,
and note that Sumproduct cannot use whole columns (A:A)

Where you are dealing with text values, e.g. your 2a, you need to wrap the
value in quotes as below.
=SUMPRODUCT(--(A2:A7="2a"),--(B2:B7=37),--(C2:C7))/
SUMPRODUCT(--(A2:A7=2),--(B2:B7=37))

You could put the values, 2, 37 etc in cells, and use those cells in the
formula instead e.g. A2:A7=E1

Alternatively you could create a Pivot Table.
Mark your block of data. Data>Pivot Tables>Finish
Drag Priority to the Column area
Drag Week to the Row area
Drag Time to the Data area
Double click on the Sum of Time filed that is created, and select average
from the List.
You will now have a summary for all weeks and priorities.

If you have more Priority items than Week items, then you could change the
order by putting Week as a Column Field and Priority as a Row Field.

 

[Rebekah]

Hi... I went with the pivot in the end... I was trying to avoid it but have
decided to run a pivot and make the cells in the table I was working in link
to the results of the pivot...

Many thanks for your help though!

 

[Roger Govier]

Hi Rebekah

I went with the pivot in the end...

A wise choice IMO.
Using the GetPivotData function will allow you pick up any of the values
from the PT.