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:

date	1	2	3	4	5	6	7	8	9	10	11	12	13	14
temp	22	23			23	24	22	23	24			25		30
rh	65	60			60	63	65	60	63			66		45

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

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]

date	1	2	3	4	5	6	7	8	9	10	11	12	13	14
temp	22	23			23	24	22	23	24			25		30
rh	65	60			60	63	65	60	63			66		45

<tbody>
</tbody>

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.