Need help: how to semi-automatically replace an empty value in a row with a row average?

[Fred]

I have an excel data file which has about 50 columns and more than 1000
rows, in some rows, values are missing at different columns. I need to
replace those empty values in each row with a row average. How do I do
that?
Thanks,
Fred

[Bernie Deitrick]

Fred,

Insert a new column A, and in cell A2 (Assuming your numbers start in row 2) enter the formula

=Average(B2:IV2)

and copy down to match your 1000+ rows.

Then select your data table in columns B through your last column, use Edit / Go To... Blanks,
press OK, then type =indirect("A" & ROW()) and then press Ctrl-Enter. Then select columns B through
last column again, copy, then pastespecial values, delete your new column A, and you're done.

HTH,
Bernie
MS Excel MVP


"Fred" <[email protected]> wrote in message
news:[email protected]...
>I have an excel data file which has about 50 columns and more than 1000
> rows, in some rows, values are missing at different columns. I need to
> replace those empty values in each row with a row average. How do I do
> that?
> Thanks,
> Fred
>