Variable Data in Equations
I have some data that contains some noise, then good data then noise again. I
need to know how to perform a calculation on the good data while ignoring the noise. The ammont of the noise/bad data is variable, so I need to be able to vary the start and end of the formula. To simplify say I have the DATA 30,20,39,2,23,1,2,3,4,5,6,7,8,9,10,32,23,43 in a columb, the data I want is 1-10 and ignoring the rest of the results. Then I want to process the processed data to produce a Y=Mx+C formula (LINEST). The data might have 2 noise numbers at the start, or 10 noise number that need ignoring (I do this by IF greater than 0.05 or negative " "). If I set LINEST to go from cell 1 to 100 the ignored data results in #VALUE as " " even though blank is classed as text not numerical. Does anyone have a way of processing this data? The data come in a colum which I process to another colum to take out the noise with the function IF difference between cell above and below greater then 0.05, or negative then " ". On the processed data I perform an Array function LINEST to produce a linear representation of the data which I want to perform on all the good data, but currently I have to set it to start at cell 20 and finish at cell 700. If there is noise present after cell 20 LINEST displays #VALUE as " " is classed as a text entry. |
Variable Data in Equations
On Dec 4, 6:56*am, Baffeled
wrote: I have some data that contains some noise, then good data then noise again. I need to know how to perform a calculation on the good data while ignoring the noise. The ammont of the noise/bad data is variable, so I need to be able to vary the start and end of the formula. To simplify say I have the DATA 30,20,39,2,23,1,2,3,4,5,6,7,8,9,10,32,23,43 in a columb, the data I want is 1-10 and ignoring the rest of the results.. Then I want to process the processed data to produce a Y=Mx+C formula (LINEST). The data might have 2 noise numbers at the start, or 10 noise number that need ignoring (I do this by IF greater than 0.05 or negative " "). If I set LINEST to go from cell 1 to 100 the ignored data results in #VALUE as " " even though blank is classed as text not numerical. Does anyone have a way of processing this data? The data come in a colum which I process to another colum to take out the noise with the function IF difference between cell above and below greater then 0.05, or negative then " ". On the processed data I perform an Array function LINEST to produce a linear representation of the data which I want to perform on all the good data, but currently I have to set it to start at cell 20 and finish at cell 700. If there is noise present after cell 20 LINEST displays #VALUE as " " is classed as a text entry. I am not exactly sure what you are trying to do but I will try to help you. It sounds like you need to get rid of all of the "" and consolidate your data. If this is the case then you can use for each, like this. This has not been tested so beware. Sub Clear() Dim i Dim Rng As Range Set Rng = Range("A1:A43") 'You will have to put in your own range here. For Each i in Rng If i = "" then i.Delete Shift:=XLup Next i End Sub |
All times are GMT +1. The time now is 01:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com