A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Normalizing Excel data across rows



 
 
Thread Tools Display Modes
  #1  
Old May 13th 09, 07:35 PM posted to microsoft.public.excel.worksheet.functions
Greenstorm
external usenet poster
 
Posts: 1
Default Normalizing Excel data across rows

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.
Ads
  #2  
Old May 13th 09, 07:45 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,501
Default Normalizing Excel data across rows

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" wrote:

> 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.

  #3  
Old May 13th 09, 08:04 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default Normalizing Excel data across rows

Greenstorm wrote:
> 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.


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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Normalizing Data k1ngr Excel Discussion (Misc queries) 6 December 17th 08 03:21 PM
Normalizing the y axis ragtopcaddy Charts and Charting in Excel 2 March 24th 06 04:25 PM
normalizing data -- a twist Brad Excel Worksheet Functions 6 June 4th 05 03:42 PM
normalizing data 4gokycats Excel Discussion (Misc queries) 1 March 27th 05 03:09 AM
Normalizing data formula? Jessica Excel Discussion (Misc queries) 1 January 25th 05 10:48 PM


All times are GMT +1. The time now is 10:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.