Normalize With Missing Values

[Thomas Scott]

I am trying to normalize my data with the min-max method (0-1) and use this formula: =(Raw!B2-MIN(Raw!B:B))/(MAX(Raw!B:B)-MIN(Raw!B:B))
where Raw is the first worksheet and the results are in worksheet #2. It works fine when copying to all cells in the recipient sheet, except for a missing value. If I delete a value in Column B I get a negative number. I need to be able to normalize with the presence of missing values where typically I use a blank or empty cell which is accepted as missing by my analytical software for further computations. Thanks.

 

[Micron]

IF((Raw!B2-MIN(Raw!B:B))/(MAX(Raw!B:B)-MIN(Raw!B:B))=0,?,(Raw!B2-MIN(Raw!B:B))/(MAX(Raw!B:B)-MIN(Raw!B:B)))

where ? is what you'd use if the cell is empty?
? could also be (Raw!B2-MIN(Raw!B:B))/(MAX(Raw!B:B)-MIN(Raw!B:B))*-1 ?

 

[Thomas Scott]

That works fine. I thought I needed an IF statement but did not think an empty cell would work. I was thinking more along the line of COUNTIF or some test for an empty cell. Thanks very much. Case closed.

 

[Micron]

Glad you were able to make sense of that. Upon reviewing, I think I should have bolded the ? or used some other place holder. Looks a bit confusing now!