Hi everyone,
To learn excel beyond a basic level I figure you need a project that is meaningful to your work. I work with large lead acid batteries. There are two of these batteries and each battery is ~250 individual cells. Monthly each battery is manually checked for cell temperature, cell voltage and specific gravity. This information has been stored in an excel file that represents the original page that was hand written from 1960-1996. Around 1996 the pages were made in excel to reflect the original hand written log sheet.
One of the things I like to do is try and figure out the health of the battery cells from the snap shots of data that are taken. What I am initially struggling with is how to arrange the data. Originally I had the date across the top in a merged cell with voltage, specific gravity and temperature in three columns under the date. The rows were each cell number. 1-250. I have read, watched videos and listened to podcasts and worked out that I need to have the data in a table with one of a kind names, and I think that i did not have the arrangement correct so I have changed it to the following.
The data has been broken into six tables each in its own worksheet, each table, sheet, and column has a name that is its own. The same date would appear on each sheet once
Sheets
SGB1 = Cell Specific gravity battery 1
SGB2 = Cell Specific gravity battery 2
VoltB1 = Cell voltage battery 1
VoltB2 = Cell voltage battery 2
TempB1 = Cell temp battery 1
TempB1 = Cell temp battery 2
Here is an example of some of the data that is in the tables. Each table is named, the first one is SGBatt1. I pasted a sample in each cell accidentally and figured it may be ok for my question.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 823"]
<colgroup><col><col span="9"><col></colgroup><tbody>[TR]
[TD]SGB1Date[/TD]
[TD]SGB1C1[/TD]
[TD]SGB1C2[/TD]
[TD]SGB1C3[/TD]
[TD]SGB1C4[/TD]
[TD]SGB1C5[/TD]
[TD]SGB1C6[/TD]
[TD]SGB1C7[/TD]
[TD]SGB1C8[/TD]
[TD]SGB1C9[/TD]
[TD]SGB1C10[/TD]
[/TR]
[TR]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.295[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.291[/TD]
[TD="align: right"]1.291[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-18[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.295[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[/TR]
[TR]
[TD="align: right"]25-Oct-18[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.291[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.289[/TD]
[/TR]
[TR]
[TD="align: right"]16-Jan-19[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.300[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.301[/TD]
[TD="align: right"]1.301[/TD]
[TD="align: right"]1.304[/TD]
[TD="align: right"]1.300[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.297[/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar-19[/TD]
[TD="align: right"]1.287[/TD]
[TD="align: right"]1.289[/TD]
[TD="align: right"]1.289[/TD]
[TD="align: right"]1.287[/TD]
[TD="align: right"]1.281[/TD]
[TD="align: right"]1.290[/TD]
[TD="align: right"]1.291[/TD]
[TD="align: right"]1.281[/TD]
[TD="align: right"]1.288[/TD]
[TD="align: right"]1.288[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-19[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.295[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.297[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.293[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 722"]
<colgroup><col><col span="9"><col></colgroup><tbody>[TR]
[TD]VB1Date[/TD]
[TD]VB1C1[/TD]
[TD]VB1C2[/TD]
[TD]VB1C3[/TD]
[TD]VB1C4[/TD]
[TD]VB1C5[/TD]
[TD]VB1C6[/TD]
[TD]VB1C7[/TD]
[TD]VB1C8[/TD]
[TD]VB1C9[/TD]
[TD]VB1C10[/TD]
[/TR]
[TR]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.128[/TD]
[TD="align: right"]2.129[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.131[/TD]
[TD="align: right"]2.131[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.125[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-18[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[/TR]
[TR]
[TD="align: right"]25-Oct-18[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.124[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.120[/TD]
[/TR]
[TR]
[TD="align: right"]16-Jan-19[/TD]
[TD="align: right"]2.126[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.128[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.126[/TD]
[TD="align: right"]2.125[/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar-19[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.120[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.120[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.120[/TD]
[TD="align: right"]2.120[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-19[/TD]
[TD="align: right"]2.118[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.124[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.118[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 720"]
<colgroup><col><col span="9"><col></colgroup><tbody>[TR]
[TD]TB1Date[/TD]
[TD]TB1C1[/TD]
[TD]TB1C2[/TD]
[TD]TB1C3[/TD]
[TD]TB1C4[/TD]
[TD]TB1C5[/TD]
[TD]TB1C6[/TD]
[TD]TB1C7[/TD]
[TD]TB1C8[/TD]
[TD]TB1C9[/TD]
[TD]TB1C10[/TD]
[/TR]
[TR]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]29.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]29.0[/TD]
[TD="align: right"]29.0[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-18[/TD]
[TD="align: right"]25.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]27.0[/TD]
[TD="align: right"]27.0[/TD]
[TD="align: right"]27.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]25.0[/TD]
[TD="align: right"]25.0[/TD]
[/TR]
[TR]
[TD="align: right"]25-Oct-18[/TD]
[TD="align: right"]34.4[/TD]
[TD="align: right"]36.0[/TD]
[TD="align: right"]36.4[/TD]
[TD="align: right"]36.2[/TD]
[TD="align: right"]36.3[/TD]
[TD="align: right"]36.7[/TD]
[TD="align: right"]36.7[/TD]
[TD="align: right"]35.7[/TD]
[TD="align: right"]35.6[/TD]
[TD="align: right"]35.8[/TD]
[/TR]
[TR]
[TD="align: right"]16-Jan-19[/TD]
[TD="align: right"]36.6[/TD]
[TD="align: right"]39.3[/TD]
[TD="align: right"]39.8[/TD]
[TD="align: right"]39.8[/TD]
[TD="align: right"]40.2[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]39.5[/TD]
[TD="align: right"]38.5[/TD]
[TD="align: right"]38.1[/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar-19[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]31.1[/TD]
[TD="align: right"]30.7[/TD]
[TD="align: right"]31.3[/TD]
[TD="align: right"]31.4[/TD]
[TD="align: right"]30.8[/TD]
[TD="align: right"]30.4[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-19[/TD]
[TD="align: right"]39.3[/TD]
[TD="align: right"]41.2[/TD]
[TD="align: right"]41.5[/TD]
[TD="align: right"]41.7[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]42.6[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]41.6[/TD]
[TD="align: right"]41.8[/TD]
[TD="align: right"]42.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
When I look at this info in the format I originally used I would determine the following.
Average of all the cells
Highest
Lowest
What cells are >= 10 points specific gravity (0.010)
The next questions would be related to qualifying the data, I dont take the readings only work with them after they have been taken, written by hand and typed. The reality is transposition errors do happen. This is difficult to easily identify. It would involve looking at previous readings from the past three months for example to see how the cell has been trending compared to the average or surrounding cells. Another method I have used is by looking at the cell voltage. There is a correlation between open circuit voltage and specific gravity.
There is a general rule that says specific gravity = cell open-circuit voltage - 0.845
This is very general for me as these cells I am looking at are not sealed but vented, and I believe that the level of electrolyte has an affect on this value. What i have done in the past is look for what is the ideal value that has the closest to zero across the entire range of one battery cells in a reading. I then use this to determine how likely a reading is to being correct or that a transposition error may have taken place.
Long winded I know but when your excited about what your up to it happens.
So the simple question is I have lots of cells in two batteries, readings on each cell are taken each month. These readings are voltage, specific gravity and temperature. What is the best way to arrange this data so I can later analyze it to see"what pops!" Is it better to have each type of data on a separate worksheet? should the cells be horizontal and date vertical?
A later project I would be trying is to get power query to pull the data from the files I receive and populate the new workbook for analysis. I had been doing this by using a macro to pull in the data but the macro mainly was only avoiding all the select/copy/paste that was happening. I would also have to edit the macro each use to change the file names, I know now that there is an easier way. I just need to get the ground work sorted so I know I am heading in the right direction.
Thanks for any advice in advance
John
To learn excel beyond a basic level I figure you need a project that is meaningful to your work. I work with large lead acid batteries. There are two of these batteries and each battery is ~250 individual cells. Monthly each battery is manually checked for cell temperature, cell voltage and specific gravity. This information has been stored in an excel file that represents the original page that was hand written from 1960-1996. Around 1996 the pages were made in excel to reflect the original hand written log sheet.
One of the things I like to do is try and figure out the health of the battery cells from the snap shots of data that are taken. What I am initially struggling with is how to arrange the data. Originally I had the date across the top in a merged cell with voltage, specific gravity and temperature in three columns under the date. The rows were each cell number. 1-250. I have read, watched videos and listened to podcasts and worked out that I need to have the data in a table with one of a kind names, and I think that i did not have the arrangement correct so I have changed it to the following.
The data has been broken into six tables each in its own worksheet, each table, sheet, and column has a name that is its own. The same date would appear on each sheet once
Sheets
SGB1 = Cell Specific gravity battery 1
SGB2 = Cell Specific gravity battery 2
VoltB1 = Cell voltage battery 1
VoltB2 = Cell voltage battery 2
TempB1 = Cell temp battery 1
TempB1 = Cell temp battery 2
Here is an example of some of the data that is in the tables. Each table is named, the first one is SGBatt1. I pasted a sample in each cell accidentally and figured it may be ok for my question.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 823"]
<colgroup><col><col span="9"><col></colgroup><tbody>[TR]
[TD]SGB1Date[/TD]
[TD]SGB1C1[/TD]
[TD]SGB1C2[/TD]
[TD]SGB1C3[/TD]
[TD]SGB1C4[/TD]
[TD]SGB1C5[/TD]
[TD]SGB1C6[/TD]
[TD]SGB1C7[/TD]
[TD]SGB1C8[/TD]
[TD]SGB1C9[/TD]
[TD]SGB1C10[/TD]
[/TR]
[TR]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.295[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.291[/TD]
[TD="align: right"]1.291[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-18[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.295[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[/TR]
[TR]
[TD="align: right"]25-Oct-18[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.291[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.292[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.289[/TD]
[/TR]
[TR]
[TD="align: right"]16-Jan-19[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.300[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.301[/TD]
[TD="align: right"]1.301[/TD]
[TD="align: right"]1.304[/TD]
[TD="align: right"]1.300[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.297[/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar-19[/TD]
[TD="align: right"]1.287[/TD]
[TD="align: right"]1.289[/TD]
[TD="align: right"]1.289[/TD]
[TD="align: right"]1.287[/TD]
[TD="align: right"]1.281[/TD]
[TD="align: right"]1.290[/TD]
[TD="align: right"]1.291[/TD]
[TD="align: right"]1.281[/TD]
[TD="align: right"]1.288[/TD]
[TD="align: right"]1.288[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-19[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.295[/TD]
[TD="align: right"]1.293[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.296[/TD]
[TD="align: right"]1.299[/TD]
[TD="align: right"]1.297[/TD]
[TD="align: right"]1.294[/TD]
[TD="align: right"]1.293[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 722"]
<colgroup><col><col span="9"><col></colgroup><tbody>[TR]
[TD]VB1Date[/TD]
[TD]VB1C1[/TD]
[TD]VB1C2[/TD]
[TD]VB1C3[/TD]
[TD]VB1C4[/TD]
[TD]VB1C5[/TD]
[TD]VB1C6[/TD]
[TD]VB1C7[/TD]
[TD]VB1C8[/TD]
[TD]VB1C9[/TD]
[TD]VB1C10[/TD]
[/TR]
[TR]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.128[/TD]
[TD="align: right"]2.129[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.131[/TD]
[TD="align: right"]2.131[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.125[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-18[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[TD="align: right"]2.100[/TD]
[/TR]
[TR]
[TD="align: right"]25-Oct-18[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.124[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.120[/TD]
[/TR]
[TR]
[TD="align: right"]16-Jan-19[/TD]
[TD="align: right"]2.126[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.128[/TD]
[TD="align: right"]2.125[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.127[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.126[/TD]
[TD="align: right"]2.125[/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar-19[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.120[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.120[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.130[/TD]
[TD="align: right"]2.120[/TD]
[TD="align: right"]2.120[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-19[/TD]
[TD="align: right"]2.118[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.124[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.119[/TD]
[TD="align: right"]2.118[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 720"]
<colgroup><col><col span="9"><col></colgroup><tbody>[TR]
[TD]TB1Date[/TD]
[TD]TB1C1[/TD]
[TD]TB1C2[/TD]
[TD]TB1C3[/TD]
[TD]TB1C4[/TD]
[TD]TB1C5[/TD]
[TD]TB1C6[/TD]
[TD]TB1C7[/TD]
[TD]TB1C8[/TD]
[TD]TB1C9[/TD]
[TD]TB1C10[/TD]
[/TR]
[TR]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]29.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]29.0[/TD]
[TD="align: right"]29.0[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-18[/TD]
[TD="align: right"]25.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]27.0[/TD]
[TD="align: right"]27.0[/TD]
[TD="align: right"]27.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]25.0[/TD]
[TD="align: right"]25.0[/TD]
[/TR]
[TR]
[TD="align: right"]25-Oct-18[/TD]
[TD="align: right"]34.4[/TD]
[TD="align: right"]36.0[/TD]
[TD="align: right"]36.4[/TD]
[TD="align: right"]36.2[/TD]
[TD="align: right"]36.3[/TD]
[TD="align: right"]36.7[/TD]
[TD="align: right"]36.7[/TD]
[TD="align: right"]35.7[/TD]
[TD="align: right"]35.6[/TD]
[TD="align: right"]35.8[/TD]
[/TR]
[TR]
[TD="align: right"]16-Jan-19[/TD]
[TD="align: right"]36.6[/TD]
[TD="align: right"]39.3[/TD]
[TD="align: right"]39.8[/TD]
[TD="align: right"]39.8[/TD]
[TD="align: right"]40.2[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]39.5[/TD]
[TD="align: right"]38.5[/TD]
[TD="align: right"]38.1[/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar-19[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]31.0[/TD]
[TD="align: right"]31.1[/TD]
[TD="align: right"]30.7[/TD]
[TD="align: right"]31.3[/TD]
[TD="align: right"]31.4[/TD]
[TD="align: right"]30.8[/TD]
[TD="align: right"]30.4[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-19[/TD]
[TD="align: right"]39.3[/TD]
[TD="align: right"]41.2[/TD]
[TD="align: right"]41.5[/TD]
[TD="align: right"]41.7[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]42.6[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]41.6[/TD]
[TD="align: right"]41.8[/TD]
[TD="align: right"]42.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
When I look at this info in the format I originally used I would determine the following.
Average of all the cells
Highest
Lowest
What cells are >= 10 points specific gravity (0.010)
The next questions would be related to qualifying the data, I dont take the readings only work with them after they have been taken, written by hand and typed. The reality is transposition errors do happen. This is difficult to easily identify. It would involve looking at previous readings from the past three months for example to see how the cell has been trending compared to the average or surrounding cells. Another method I have used is by looking at the cell voltage. There is a correlation between open circuit voltage and specific gravity.
There is a general rule that says specific gravity = cell open-circuit voltage - 0.845
This is very general for me as these cells I am looking at are not sealed but vented, and I believe that the level of electrolyte has an affect on this value. What i have done in the past is look for what is the ideal value that has the closest to zero across the entire range of one battery cells in a reading. I then use this to determine how likely a reading is to being correct or that a transposition error may have taken place.
Long winded I know but when your excited about what your up to it happens.
So the simple question is I have lots of cells in two batteries, readings on each cell are taken each month. These readings are voltage, specific gravity and temperature. What is the best way to arrange this data so I can later analyze it to see"what pops!" Is it better to have each type of data on a separate worksheet? should the cells be horizontal and date vertical?
A later project I would be trying is to get power query to pull the data from the files I receive and populate the new workbook for analysis. I had been doing this by using a macro to pull in the data but the macro mainly was only avoiding all the select/copy/paste that was happening. I would also have to edit the macro each use to change the file names, I know now that there is an easier way. I just need to get the ground work sorted so I know I am heading in the right direction.
Thanks for any advice in advance
John