Average including zero after paste link tranpose

[pipsiphone]

Dear wise excel people,

I need help with an average formula that will give an accurate answer. My issue is that I need an average of what has been entered rather than for the entire selection. I have paste link transposed the data as it needs to run on Excel 2003 which can only hold 30 items in the Average(number1, number 2 ... etc) and I need 52. My problem occurs when the data is paste linked all blank cells appear as a Zero therefore increasing the count of the average and providing a wrong answer.

Eg when (1, 2, 0, 3) Average = 1.5 is entered the formula works out (1, 2, 0, 3, 0, 0, 0, 0, 0, 0, etc) Average = 0.12 because it divides by 52 instead of 4.
As you can see I can’t ignore zeros as they are a possible input. What would be perfect is for the paste link to paste the blank cells instead of the providing a zero then the plan =Average(B3:B55) would be fine.

If possible a formula would be the most helpful response as I don’t have a clue about macros and have previously seen some answers given with suggestions in that style.

Any help would be greatly appreciated!! Thanks

(Note: the new entered data is on the same row but different columns hence the reason for the paste link transpose to get all the data in one column)

 

[barry houdini]

Hello pipsiphone, welcome to MrExcel

I'm not quite clear why you have to move the data, can you not average it where it is, in a single row? Which cells contain the data you want to average?

If you have more than 30 separate cells you can still average those, you can use a "union" within the AVERAGE function by enclosing some cells in brackets, those are then counted as a single reference, e.g. for 52 numbers

=AVERAGE(n1,n2,n3....n22,(n23,n24,n25.....n52))

 

[pipsiphone]

Thanks you for the kind welcome, I have by more luck than gd judgement managed to solve my problem by entering a ' into each of the cells that data will be entered into in the future. Thank you again!!!