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?

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

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

Hi,

Not knowing the cell placement

try this

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

Cheers

ratcat

Originally Posted by ratcat

Hi,

Not knowing the cell placement

try this

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

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"

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.

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

Clear as mud ??