OFFSET and Blank Spaces.

[yoges]

Hi,

I've only recently started exploring excel functions and i need help with my daily logging chart. Im trying to generate a dynamic chart with the following data using OFFSET and COUNT functions for the recent 7 days. My data is as follows:

[TABLE="class: grid, width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]date[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64, align: right"]9[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64, align: right"]11[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]13[/TD]
[TD="width: 64, align: right"]14[/TD]
[/TR]
[TR]
[TD]temp[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]rh[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]

and these are my defined named formulas:

=OFFSET(Sheet1!$A$3,0,COUNT(Sheet1!$3:$3),1,-7)

=OFFSET(Sheet1!$A$2,0,COUNT(Sheet1!$2:$2),1,-7)

The problem is when all the cells are filled with a value it works fine but when the data has blank cells(for weekends and holidays) then it does not work. I've tried reading through forums and watching videos but i couldn't find a solution to this. Can anyone help me understand and if possible provide a solution for this?

More details on the chart:
1) the blank dates need to be shown
2) the blank points will be connected to the next available data value

Thank you.

 

[DanteAmor]

Re: OFFSET and Blank Spaces. Need Help

row 2 always has data, then use 2, or if row 1 always has data then use the 1. test and tell me.
Try:

=OFFSET(Sheet1!$A$3,0,COUNT(Sheet1!$2:$2),1,-7)

 

[yoges]

Re: OFFSET and Blank Spaces. Need Help

row 2 always has data, then use 2, or if row 1 always has data then use the 1. test and tell me.
Try:

=OFFSET(Sheet1!$A$3,0,COUNT(Sheet1!$2:$2),1,-7)

Hi Dante,

my rows will always have blank cells due to unavailable data during weekends and holidays and these blank data also need to be shown in the line chart. As i have mentioned before if all the cells in my rows have value then the formula works fine but when i have blank cells it somehow only detects the last data to be up until column 9.

Thanks

 

[DanteAmor]

[TABLE="class: grid, width: 960"]
<tbody>[TR]
[TD="width: 64"]date[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64, align: right"]9[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64, align: right"]11[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]13[/TD]
[TD="width: 64, align: right"]14[/TD]
[/TR]
[TR]
[TD]temp[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]rh[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]

But in this example the row "date" always has data, is it correct?

 

[yoges]

But in this example the row "date" always has data, is it correct?

Yes correct, i just realized i could use the date as the main reference instead of the data. You have been great help. Thank you Dante.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.