Normalizing Excel data across rows

  • Thread starter Thread starter Greenstorm
  • Start date Start date
G

Greenstorm

I have a large spreadsheet (96 by 100) in Excel 2007 of recorded electrical
data. Each row represents data taken from a single source, and each column
represents a time point. I would like to normalize data across each row (i.e.
assign the max value in that row 1, and the min value 0, and distribute the
intermediate values accordingly). This needs to be done for each row
independently and I can't for the life of me figure out how.
 
hi,

I would do it in a seperate table outside of your original date. lets say
you first row of data are in A1 to Z1. In a cell lower doen the sheet (say)
a100 enter the formula

=max(a1:z1)

then in Z100 enter the formula

Min(a1:z1)

Now select these 2 newly created cells and the empty cells in between and then

Edit|Fill - Series and ensure Rows & Linear are selected and Excel will have
calculated the step. Click OK and your data will fill normalised.

Mike
 
hi,

I would do it in a seperate table outside of your original date. lets say
you first row of data are in A1 to Z1. In a cell lower doen the sheet (say)
a100 enter the formula

=max(a1:z1)

then in Z100 enter the formula

Min(a1:z1)

Now select these 2 newly created cells and the empty cells in between and then

Edit|Fill - Series and ensure Rows & Linear are selected and Excel will have
calculated the step. Click OK and your data will fill normalised.

Mike
 
Greenstorm said:
I have a large spreadsheet (96 by 100) in Excel 2007 of recorded electrical
data. Each row represents data taken from a single source, and each column
represents a time point. I would like to normalize data across each row (i.e.
assign the max value in that row 1, and the min value 0, and distribute the
intermediate values accordingly). This needs to be done for each row
independently and I can't for the life of me figure out how.
Click to expand...

I think you are looking for something like this, pasted into a blank sheet:


=IF('Sheet 1'!A1<>"",('Sheet 1'!A1-MIN('Sheet 1'!1:1))/
(MAX('Sheet 1'!1:1)-MIN('Sheet 1'!1:1)),"")


Assumes your data is on 'Sheet 1' and starts in cell A1. Adjust as needed.
 
Greenstorm said:
I have a large spreadsheet (96 by 100) in Excel 2007 of recorded electrical
data. Each row represents data taken from a single source, and each column
represents a time point. I would like to normalize data across each row (i.e.
assign the max value in that row 1, and the min value 0, and distribute the
intermediate values accordingly). This needs to be done for each row
independently and I can't for the life of me figure out how.
Click to expand...

I think you are looking for something like this, pasted into a blank sheet:


=IF('Sheet 1'!A1<>"",('Sheet 1'!A1-MIN('Sheet 1'!1:1))/
(MAX('Sheet 1'!1:1)-MIN('Sheet 1'!1:1)),"")


Assumes your data is on 'Sheet 1' and starts in cell A1. Adjust as needed.
 
Back
Top