A different type of excel question that may be a yup that is how the data should be laid out...

Johnny K

New Member
Joined
Apr 19, 2010
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 it’s 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.
SGB1DateSGB1C1SGB1C2SGB1C3SGB1C4SGB1C5SGB1C6SGB1C7SGB1C8SGB1C9SGB1C10
18-Jul-181.2931.2931.2941.2931.2941.2951.2961.2921.2911.291
24-Aug-181.2921.2921.2931.2931.2931.2941.2951.2941.2921.292
25-Oct-181.2921.2911.2921.2921.2921.2921.2941.2941.2931.289
16-Jan-191.2991.2991.3001.2991.3011.3011.3041.3001.2961.297
17-Mar-191.2871.2891.2891.2871.2811.2901.2911.2811.2881.288
17-Apr-191.2931.2931.2951.2931.2961.2961.2991.2971.2941.293

<colgroup><col><col span="9"><col></colgroup><tbody>
</tbody>
VB1DateVB1C1VB1C2VB1C3VB1C4VB1C5VB1C6VB1C7VB1C8VB1C9VB1C10
18-Jul-182.1272.1282.1292.1272.1312.1312.1302.1272.1252.125
24-Aug-182.1002.1002.1002.1002.1002.1002.1192.1002.1002.100
25-Oct-182.1212.1222.1242.1212.1252.1232.1252.1252.1212.120
16-Jan-192.1262.1272.1282.1252.1302.1272.1302.1302.1262.125
17-Mar-192.1302.1202.1302.1202.1302.1302.1302.1302.1202.120
17-Apr-192.1182.1192.1222.1192.1232.1212.1242.1232.1192.118

<colgroup><col><col span="9"><col></colgroup><tbody>
</tbody>
TB1DateTB1C1TB1C2TB1C3TB1C4TB1C5TB1C6TB1C7TB1C8TB1C9TB1C10
18-Jul-1829.030.030.030.030.030.031.030.029.029.0
24-Aug-1825.026.027.027.027.026.026.026.025.025.0
25-Oct-1834.436.036.436.236.336.736.735.735.635.8
16-Jan-1936.639.339.839.840.240.340.339.538.538.1
17-Mar-1930.031.031.031.031.130.731.331.430.830.4
17-Apr-1939.341.241.541.742.542.642.541.641.842.0

<colgroup><col><col span="9"><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

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 don’t 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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Perhaps if you can, make your data look like the below. So, just have four columns (you can do this with your current data sets by using the power query feature 'Unpivot Columns'). And this could all be in one table, instead of having multiple tables in multiple worksheets. Not sure the additional analysis you might have to do with this data, but having your data setup like below will allow you to manipulate the data easily in virtually in manner you'd like. Reach out with any questions. Hope this helps.

TypeDateAttributeValue
SGB17/18/2018C11.293
SGB17/18/2018C21.293
SGB17/18/2018C31.294
SGB17/18/2018C41.293
SGB17/18/2018C51.294
SGB17/18/2018C61.295
SGB17/18/2018C71.296
SGB17/18/2018C81.292
SGB17/18/2018C91.291
SGB17/18/2018C101.291
SGB18/24/2018C11.292
SGB18/24/2018C21.292
SGB18/24/2018C31.293
SGB18/24/2018C41.293
SGB18/24/2018C51.293
SGB18/24/2018C61.294
SGB18/24/2018C71.295
SGB18/24/2018C81.294
SGB18/24/2018C91.292
SGB18/24/2018C101.292
SGB110/25/2018C11.292
SGB110/25/2018C21.291
SGB110/25/2018C31.292
SGB110/25/2018C41.292
SGB110/25/2018C51.292
SGB110/25/2018C61.292
SGB110/25/2018C71.294
SGB110/25/2018C81.294
SGB110/25/2018C91.293
SGB110/25/2018C101.289
SGB11/16/2019C11.299
SGB11/16/2019C21.299
SGB11/16/2019C31.3
SGB11/16/2019C41.299
SGB11/16/2019C51.301
SGB11/16/2019C61.301
SGB11/16/2019C71.304
SGB11/16/2019C81.3
SGB11/16/2019C91.296
SGB11/16/2019C101.297
SGB13/17/2019C11.287
SGB13/17/2019C21.289
SGB13/17/2019C31.289
SGB13/17/2019C41.287
SGB13/17/2019C51.281
SGB13/17/2019C61.29
SGB13/17/2019C71.291
SGB13/17/2019C81.281
SGB13/17/2019C91.288
SGB13/17/2019C101.288
SGB14/17/2019C11.293
SGB14/17/2019C21.293
SGB14/17/2019C31.295
SGB14/17/2019C41.293
SGB14/17/2019C51.296
SGB14/17/2019C61.296
SGB14/17/2019C71.299
SGB14/17/2019C81.297
SGB14/17/2019C91.294
SGB14/17/2019C101.293
VB17/18/2018C12.127
VB17/18/2018C22.128
VB17/18/2018C32.129
VB17/18/2018C42.127
VB17/18/2018C52.131
VB17/18/2018C62.131
VB17/18/2018C72.13
VB17/18/2018C82.127
VB17/18/2018C92.125
VB17/18/2018C102.125
VB18/24/2018C12.1
VB18/24/2018C22.1
VB18/24/2018C32.1
VB18/24/2018C42.1
VB18/24/2018C52.1
VB18/24/2018C62.1
VB18/24/2018C72.119
VB18/24/2018C82.1
VB18/24/2018C92.1
VB18/24/2018C102.1
VB110/25/2018C12.121
VB110/25/2018C22.122
VB110/25/2018C32.124
VB110/25/2018C42.121
VB110/25/2018C52.125
VB110/25/2018C62.123
VB110/25/2018C72.125
VB110/25/2018C82.125
VB110/25/2018C92.121
VB110/25/2018C102.12
VB11/16/2019C12.126
VB11/16/2019C22.127
VB11/16/2019C32.128
VB11/16/2019C42.125
VB11/16/2019C52.13
VB11/16/2019C62.127
VB11/16/2019C72.13
VB11/16/2019C82.13
VB11/16/2019C92.126
VB11/16/2019C102.125
VB13/17/2019C12.13
VB13/17/2019C22.12
VB13/17/2019C32.13
VB13/17/2019C42.12
VB13/17/2019C52.13
VB13/17/2019C62.13
VB13/17/2019C72.13
VB13/17/2019C82.13
VB13/17/2019C92.12
VB13/17/2019C102.12
VB14/17/2019C12.118
VB14/17/2019C22.119
VB14/17/2019C32.122
VB14/17/2019C42.119
VB14/17/2019C52.123
VB14/17/2019C62.121
VB14/17/2019C72.124
VB14/17/2019C82.123
VB14/17/2019C92.119
VB14/17/2019C102.118
TB17/18/2018C129
TB17/18/2018C230
TB17/18/2018C330
TB17/18/2018C430
TB17/18/2018C530
TB17/18/2018C630
TB17/18/2018C731
TB17/18/2018C830
TB17/18/2018C929
TB17/18/2018C1029
TB18/24/2018C125
TB18/24/2018C226
TB18/24/2018C327
TB18/24/2018C427
TB18/24/2018C527
TB18/24/2018C626
TB18/24/2018C726
TB18/24/2018C826
TB18/24/2018C925
TB18/24/2018C1025
TB110/25/2018C134.4
TB110/25/2018C236
TB110/25/2018C336.4
TB110/25/2018C436.2
TB110/25/2018C536.3
TB110/25/2018C636.7
TB110/25/2018C736.7
TB110/25/2018C835.7
TB110/25/2018C935.6
TB110/25/2018C1035.8
TB11/16/2019C136.6
TB11/16/2019C239.3
TB11/16/2019C339.8
TB11/16/2019C439.8
TB11/16/2019C540.2
TB11/16/2019C640.3
TB11/16/2019C740.3
TB11/16/2019C839.5
TB11/16/2019C938.5
TB11/16/2019C1038.1
TB13/17/2019C130
TB13/17/2019C231
TB13/17/2019C331
TB13/17/2019C431
TB13/17/2019C531.1
TB13/17/2019C630.7
TB13/17/2019C731.3
TB13/17/2019C831.4
TB13/17/2019C930.8
TB13/17/2019C1030.4
TB14/17/2019C139.3
TB14/17/2019C241.2
TB14/17/2019C341.5
TB14/17/2019C441.7
TB14/17/2019C542.5
TB14/17/2019C642.6
TB14/17/2019C742.5
TB14/17/2019C841.6
TB14/17/2019C941.8
TB14/17/2019C1042

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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