Data log - sorting for two parameters and divide up in intervals. Plot specific log files

Jharming

New Member
Joined
May 28, 2014
Messages
16
Hi,

First time here and just started to look into Excel and VBA due to new challenges.

The deal is:

I have a data series measuring over time. (x-axis)
To this series I have 20 something temperatures loggers. Two of those temperature loggers measuring parameters I want to sort for.
e.g.

I measure over 5 hours.
The two parameters i set I can be changed during this time period.

First hour Tm=50, Ta=70
Second hour Tm=70, Ta=70
Third hour Tm=90, Ta=70
Fourth hour Tm=90, Ta=50
Fifth hour Tm=70, Ta=40

The logger logs 20 other temperature measurements.

What I want to do is to have a chart where I can choose which loggers I want to see and which set points.

To do this I have written a basic VBA code where I simply hide/unhide the rows and columns I want to see by using TRUE/FALSE checkbox.

To make this work I need to look into the data first and determine manually when Tm, Ta or both changes and write down the specific row.
I have written in the name for each checkbox Tm and Ta. Furthermore I have written for each other logger a checkbox that hide/unhide the specific logger.



I am sure this can be done much more convenient by inspecting the Tm and Ta using an algorithm and thereby extract the rows and values (average) for Tm and Ta and set them in the checkboxes.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Describe how the data is stored in your worksheet.
Describe what data you want to extract and exactly how you want it presented.
 
Upvote 0
Describe how the data is stored in your worksheet.
Describe what data you want to extract and exactly how you want it presented.
The data is stored in columns. First column is time stamp.
the next 2 two columns are temperature measurements of the parameters I can change. Ambient temperature and media temperature. It takes several minutes to change one of those parameters.
the rest of the columns are temperature measurements at different sites.

The way I work with the data today is that I have created a macro where I hide/unhindered the columns/rows that I want in my graph. The issue is that I manually have to find the right row for each temperature step.
 
Upvote 0
You can add a helper column D that shows true if either values in columns B or C changed from the previous value in that column
Assuming a header in row 1, try this formula in D2 and down:
Code:
=IF(OR(B2<>B1,C2<>C1),TRUE,"")
It will show TRUE if either value in column B or C differed from the previous value in its respective column.

This could also be implemented in VBA code to either
1) add the helper column
2) create an array that contained therows where the values changed
 
Upvote 0
You can add a helper column D that shows true if either values in columns B or C changed from the previous value in that column
Assuming a header in row 1, try this formula in D2 and down:
Code:
=IF(OR(B2<>B1,C2<>C1),TRUE,"")
It will show TRUE if either value in column B or C differed from the previous value in its respective column.

This could also be implemented in VBA code to either
1) add the helper column
2) create an array that contained therows where the values changed

Thats interesting.

So first I should use a round function to round to nearest 10th. I get data that shows for each 1 minut interval:
40.1, 39.9, 40.0, 40.1 etc. all these values will be rounded to 40
at one time the temperature is changed 10 degress. then it becomes 42.9, 44.9, 47.9, 48.9, 50.1, 49.1 etc until next change.

btw. can you recommend a book dealing with this type of VBA code?
 
Upvote 0
Excel formulae use the actual, not the displayed values in a cell. 40.190540467635037 may display as 40 (if the cell format is set that way), but is not equal to 40. The recorded temperatures' precision may be displayed to the nearest whole number by default, but the actual value can be revealed. Format the columns so you see more decimal places to always see the actual number or select a single cell and see the actual value in the formula bar -- below the ribbon (but above the column headers).

This formula includes the round functions in it:
Code:
=IF(OR(ROUND(B2,1)<>ROUND(B1,1),ROUND(C2,1)<>ROUND(C1,1)),TRUE,"")

These books are old, but have have great info. I prefer their style to the newer editions. Most of the VBA discussion is valid, although the Excel object model has been greatly expanded in the versions later than 2003. Any books / websites by any of these authors would be good:
pbornemeier said:
Excel 2003 Power Programming with VBA (Excel Power Programming With Vba) by John Walkenbach
Professional Excel Development by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
Excel 2002 VBA: Programmers Reference by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)

Reviewing interesting sounding posts (old and new) on this site will give you a wealth of programming info in reasonably small chunks.

The podcasts, DVDs and books that this forum's parent has are excellent: MrExcel.com | Excel Resources | Excel Seminars | Excel Products
 
Upvote 0
This formula includes the round functions in it:
Code:
=IF(OR(ROUND(B2,1)<>ROUND(B1,1),ROUND(C2,1)<>ROUND(C1,1)),TRUE,"")

These books are old, but have have great info. I prefer their style to the newer editions. Most of the VBA discussion is valid, although the Excel object model has been greatly expanded in the versions later than 2003. Any books / websites by any of these authors would be good:


Reviewing interesting sounding posts (old and new) on this site will give you a wealth of programming info in reasonably small chunks.

The podcasts, DVDs and books that this forum's parent has are excellent: MrExcel.com | Excel Resources | Excel Seminars | Excel Products
Yes, I am sure there is a wealth of information on this site. The issue is just to find the correct example. Therefor I prefer to read a book because here, normally, the information is structured so it you can use less time (but some money to support the author) to find your answers.

I have just started in a new position, and I was thinking if I should go the mathlab road or simply stay in Excel, since this is the standard tool everybody use in our organisation.

I am pretty sure that if I can learn to control VBA with Excel that I can get good enough results for the purposes I need.
 
Upvote 0
You can add a helper column D that shows true if either values in columns B or C changed from the previous value in that column
Assuming a header in row 1, try this formula in D2 and down:
Code:
=IF(OR(B2<>B1,C2<>C1),TRUE,"")
It will show TRUE if either value in column B or C differed from the previous value in its respective column.

This could also be implemented in VBA code to either
1) add the helper column
2) create an array that contained therows where the values changed

ok I played a little around with code today.

I used the round function to get 40, 50 etc. But I also had to include an AND due to the slow transition it could happen that I got TRUE to fast.

I used something like, IF(AND(Cellrow-1="", cellrow-2="",...,cellrow-20=""),OR())

I had to use 20 AND's to get the TRUE the right places.

But now I have way to see when there is a temperature change.

Now I need to get these row numbers into a place where the macro can pick it up. There I am not sure how to do that.
something like an IF sentence which gives the row number if TRUE.
I would need to save each row number in a different cell.
 
Upvote 0
instead of TRUE you could put ROW()
Code:
=IF(OR(ROUND(B2,1)<>ROUND(B1,1),ROUND(C2,1)<>ROUND(C1,1)),ROW(),"")

I did not understand this: But I also had to include an AND due to the slow transition it could happen that I got TRUE to fast.
Could you post a few dozen lines of data that shows why it is needed.
 
Upvote 0
instead of TRUE you could put ROW()
Code:
=IF(OR(ROUND(B2,1)<>ROUND(B1,1),ROUND(C2,1)<>ROUND(C1,1)),ROW(),"")

I did not understand this: But I also had to include an AND due to the slow transition it could happen that I got TRUE to fast.
Could you post a few dozen lines of data that shows why it is needed.

Example for what I used for row 982
Code:
=IF(AND(X970="";X971="";X972="";X973="";X974="";X975="";X976="";X977="";X978="";X979="";X980="";X981="";X982="";X983="";X984="";X985="";X986="";X987="";OR(ROUND(B988;-1)<>ROUND(B987;-1);ROUND(C988;-1)<>ROUND(C987;-1)));TRUE;"")
[code]

If both parameters are changed. Tm and Ta, then the change times are different. Example change Tm from 90 to 110 and Ta from 40 to 80

timestep, Tm, Ta
1, 90, 40
2, 91, 45 (True)
3, 94, 48
4, 96, 52 (True)
5, 101, 56 (True)
6, 108, 62 (True)
7, 110, 66 (True)
8, 110, 72
9, 110, 78 (True)
10, 110, 80

To avoid all these TRUE, I have incoporated the AND function that looks at the twenty previous timesteps.

Regard the code that gives the row number. I guess here I can use an IF function that put the numbers in a column. The IF function should look if the cell previous is occupied and then put the number in the first empty cell.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top