Average of 2 coloums

I have 2 spreadsheets showing a row of numbers that represents the number of days an order took to process.

I want to find the average of these columns - I tried the below formula.


=ROUND(((AVERAGE(Ttool!AI:AI)+AVERAGE(Alpha!AI:AI))/2),0)&" Days"


Which seemed to work but now I realise that it is only correct if both columns have the same number of rows - Which they don't

Is there a simple way of solving this without using a series of sum & count statements?

Hi,

Not knowing the cell placement

try this

Please Login or Register  to view this content.

Cheers

ratcat

Originally Posted by ratcat

Hi,

Not knowing the cell placement

try this

Please Login or Register  to view this content.

Cheers

ratcat

Found an error

Please move the /2 out side of the next )

Wrong - (Alpha!AI:AI)/2)),0)&" Days"

Right - (Alpha!AI:AI))/2),0)&" Days"

I think this should work

=ROUND(((SUM(Thermatool!AI:AI)+SUM(Alpha!AI:AI))/((COUNT(Thermatool!AI:AI)+COUNT(Alpha!AI:AI)))),0)&" Days"

No need for Countif as AI is always going to be a number.

Seems to give the right answer

Originally Posted by ratcat

Found an error

Please move the /2 out side of the next )

Wrong - (Alpha!AI:AI)/2)),0)&" Days"

Right - (Alpha!AI:AI))/2),0)&" Days"

Just touching an EdMac said about the true averages. The formula must divided by both sumproducts or none.

Please Login or Register  to view this content.

Clear as mud ??

The sumproduct doesn't seem to work.

Currently I have 1 figure in each spreadheet

174 in thermatool & 0 in Alpha

This should give an answer of 87 days

Using Sum product I get 174.

Changing Alpha!AI Doesn't have an effect on the answer - Its just giving me an average of Thermatool(AI:AI)

now changed it to both "/2"

and with 2 figures it works fine - but as soon as one has more 2 screws up

I have

172
0


0

Should be average of 57 but sumproduct method gives 43

Which is (86+0)/2

Which is the problem I had at the start.

=ROUND(average(Thermatool!AI:AI,Alpha!AI:AI),0) &" Days"

Never thought about having 2 cell references - So simple!!


Cheers