Dynamic chart OFFSET challenge

[Peter-Jan De Smedt]

Dears

I have below formula

=OFFSET($D$4;1;0;COUNTA($D$5:$D$11)-COUNTBLANK($D$5:$D$11)) for a dynamic chart which works fine except when there is no data. When there is no data I have one column with value one and an error message saying "A formula in this worksheet contains one or more invalid reference...". The range of the chart is fed by formulas linked to other cell.

Does anyone have an idea how to avoid this?

Thanks in advance!

KR

John

 

[Hui]

John


How about:


=OFFSET($D$4;1;0;Max(1,COUNTA($D$5:$D$11)-COUNTBLANK($D$5:$D$11)))


or more likely

=If(COUNTA($D$5:$D$11)=COUNTBLANK($D$5:$D$11), 0, OFFSET($D$4;1;0;COUNTA($D$5:$D$11)- COUNTBLANK($D$5:$D$11)))


I hope you realise that this formula will return a range and hence will need to be inside something like a Sum() or other function or used as the feed to a chart

 

[Peter-Jan De Smedt]

Thanks for your time and help Hui!

Your first formula works like a charm!

KR John