Trouble with graph data

[mannsi]

Hello,
I have data in the following form

WeekNumber - Station1 - Station2 - Station3

I've got a Visual Basic code that fills up the weeknumbers up to the current week. I then use an if sentence that displays some result for Station 1-3 only if there is a weeknumber present in the same line. Otherwise it displays nothing. I copied the if sentence down through the 52 possible weeks. So far, so good.
But now I wan't to graph my data, and I use the offset function in order to obtain the data I want like this

Offset(B2,0,0,counta($B:$B)-1,1)

My problem is that the function 'counta' counts all the "empty" cells as well since they include an if sentence, even though said if sentence returns a "".

How can I get around this ?
Please let me know if anything is unclear, and thanks for your help.

 

[pgc01]

Hi

I don't understand your layout nor the type of the X values.

I Assumed:

- The table with the data is in Sheet1, Row 1 has the headers, the values start in Row 2

- X axis values in column A (WeekNumber) is composed of strings (not numbers), like {"W1","W2","W3","","",...}

- Y values in columns B,C,D (Station1 - Station2 - Station3) are numbers

Define the names:

Sheet1!chtWeekNumber =OFFSET($A$2,0,0,MATCH("*",$A:$A,-1)-1,1)
Sheet1!chtStation1 =OFFSET(Sheet1!chtWeekNumber,0,1)
Sheet1!chtStation2 =OFFSET(Sheet1!chtWeekNumber,0,2)
Sheet1!chtStation2 =OFFSET(Sheet1!chtWeekNumber,0,3)

Use these names in the chart.

HTH

 

[mannsi]

Thank you for your reply

Here is a view of my sheet, let's call it 'sheet1'.

<table x:str="" style="border-collapse: collapse; width: 212pt;" border="0" cellpadding="0" cellspacing="0" width="282"><col style="width: 86pt;" width="114"> <col style="width: 42pt;" span="3" width="56"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 86pt;" height="17" width="114">WeekNum</td> <td style="width: 42pt;" width="56"> Station1
</td> <td style="width: 42pt;" width="56">Station2
</td> <td style="width: 42pt;" width="56">Station3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1</td> <td class="xl24" x:num="52.10347222222223" align="right">1250:29</td> <td class="xl24" x:num="0.14722222222222209" align="right">3:32</td> <td class="xl24" x:num="7.9166666666666594E-2" align="right">1:54</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="5.9027777777777797E-2" align="right">1:25</td> <td class="xl24" x:num="8.3333333333333297E-3" align="right">0:12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">3</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="0.17986111111111117" align="right">4:19</td> <td class="xl24" x:num="1.8055555555555599E-2" align="right">0:26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">4</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="1.180555555555555E-2" align="right">0:17</td> <td class="xl24" x:num="7.5694444444444467E-2" align="right">1:49</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">5</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="7.569444444444437E-2" align="right">1:49</td> <td class="xl24" x:num="0.6430555555555556" align="right">15:26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">6</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="0.53194444444444478" align="right">12:46</td> <td class="xl24" x:num="0.26597222222222167" align="right">6:23</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">7</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="0.13819444444444443" align="right">3:19</td> <td class="xl24" x:num="5.9722222222222204E-2" align="right">1:26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">8</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="0.12083333333333328" align="right">2:54</td> <td class="xl24" x:num="9.7222222222222189E-3" align="right">0:14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">9</td> <td class="xl24" x:num="0.51527777777777761" align="right">12:22</td> <td class="xl24" x:num="0.281944444444444" align="right">6:46</td> <td class="xl24" x:num="1.0986111111111132" align="right">26:22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">10</td> <td class="xl24" x:num="0.14652777777777773" align="right">3:31</td> <td class="xl24" x:num="2.29166666666667E-2" align="right">0:33</td> <td class="xl24" x:num="0.21388888888888907" align="right">5:08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">11</td> <td class="xl24" x:num="0.30763888888888852" align="right">7:23</td> <td class="xl24" x:num="0.13263888888888892" align="right">3:11</td> <td class="xl24" x:num="0.10694444444444449" align="right">2:34</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">12</td> <td class="xl24" x:num="0.47708333333333286" align="right">11:27</td> <td class="xl24" x:num="5.1388888888888803E-2" align="right">1:14</td> <td class="xl24" x:num="3.5416666666666638E-2" align="right">0:51</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">13</td> <td class="xl24" x:num="0.15069444444444488" align="right">3:37</td> <td class="xl24" x:num="6.9444444444444404E-4" align="right">0:01</td> <td class="xl24" x:num="4.5138888888888895E-2" align="right">1:05</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">14</td> <td class="xl24" x:num="7.9861111111111049E-2" align="right">1:55</td> <td class="xl24" x:num="2.2534722222222214" align="right">54:05</td> <td class="xl24" x:num="0.13472222222222224" align="right">3:14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">15</td> <td class="xl24" x:num="0.24444444444444491" align="right">5:52</td> <td class="xl24" x:num="1.8749999999999999E-2" align="right">0:27</td> <td class="xl24" x:num="5.0000000000000051E-2" align="right">1:12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">16</td> <td class="xl24" x:num="0" align="right">0:00</td> <td class="xl24" x:num="2.5694444444444499E-2" align="right">0:37</td> <td class="xl24" x:num="1.5972222222222231E-2" align="right">0:23</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">17</td> <td class="xl24" x:num="0.24166666666666645" align="right">5:48</td> <td class="xl24" x:num="6.7361111111111108E-2" align="right">1:37</td> <td class="xl24" x:num="0.77430555555555558" align="right">18:35</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">18</td> <td class="xl24" x:num="3.7277777777777814" align="right">89:28</td> <td class="xl24" x:num="6.6666666666666707E-2" align="right">1:36</td> <td class="xl24" x:num="6.8055555555555605E-2" align="right">1:38</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">19</td> <td class="xl24" x:num="0.41944444444444429" align="right">10:04</td> <td class="xl24" x:num="0.17430555555555599" align="right">4:11</td> <td class="xl24" x:num="0.34652777777777777" align="right">8:19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">20</td> <td class="xl24" x:num="0.22847222222222213" align="right">5:29</td> <td class="xl24" x:num="5.4166666666666689E-2" align="right">1:18</td> <td class="xl24" x:num="0.14652777777777773" align="right">3:31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">21</td> <td class="xl24" x:num="8.5416666666666696E-2" align="right">2:03</td> <td class="xl24" x:num="0.14374999999999999" align="right">3:27</td> <td class="xl24" x:num="1.52777777777778E-2" align="right">0:22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">22</td> <td class="xl24" x:num="1.6201388888888844" align="right">38:53</td> <td class="xl24" x:num="0.35763888888888934" align="right">8:35</td> <td class="xl24" x:num="0.17569444444444449" align="right">4:13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">23</td> <td class="xl24" x:num="0.56111111111111067" align="right">13:28</td> <td class="xl24" x:num="0.69930555555555551" align="right">16:47</td> <td class="xl24" x:num="0.36249999999999999" align="right">8:42</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">24</td> <td class="xl24" x:num="3.9583333333333297E-2" align="right">0:57</td> <td class="xl24" x:num="3.0555555555555603E-2" align="right">0:44</td> <td class="xl24" x:num="0.30347222222222214" align="right">7:17</td> </tr> </tbody></table>
My problem being that in the blank rows where there is no week number there is an if sentence that gives me an "" output if there is no week number. So when I use the CountA function within an offset function, it counts my "empty" cells as well.

I haven't checked out your solution pgc01 yet. I will do so now, but I you guys see an obvious problem with his solution based on the extra information in this post, please let me know.

EDIT: After reading about the MATCH function, would it not just give me the same problem as the CountA function ? Will it not just see the if sentence in the cells return those cells as well ?

 

[pgc01]

EDIT: After reading about the MATCH function, would it not just give me the same problem as the CountA function ? Will it not just see the if sentence in the cells return those cells as well ?<!-- / message --><!-- edit note -->

No, if the X values are strings as I in my post, it will count right.

If you have the X values as numbers, as it seems in your last post (you again did not say whether the X values are numbers or strings), you can use COUNT() instead of COUNTA(). COUNT() only counts numbers.

 

[mannsi]

Sorry about my lack of clarity. Changing from CountA to Count did the trick for me. Thank you.

 

[pgc01]

I'm glad it's working now. Cheeers.