Source Data Formula needed for rolling 90 days entries Chart

[zoso]

I'm guessing the formula needed for the chart might involve the Indirect function, but I hardly know anything about this and am having real trouble putting this one together myself - hope you can help me please!

I need to put a chart together with the horizontal (X?) axis showing only the last 90 dates I have been out running (in bar format), so that when for example I go out tonight and enter the info the entry from 3 months previously will drop out of the chart.

The vertical (Y?) axis needs to show 2 sets of data: the distance and the time for each date on the horizontal axis. The format of the distance is in miles to 1 decimal place.

Please note that the format of the pace is m:ss but the ' :ss ' needs to be converted to a decimal for chart.

Worksheet 'Training Log' Column A contains the dates, Column C contains the distance and Column E the pace, with Row 1341 being the first empty one.

Hope you can help!

 

[inarbeth]

Re: Source Data Formula needed for rolling 90 days entries C

For the first part of your question have a look at this thread: http://216.92.17.166/board2/viewtopic.php?t=53513

If you use a defined name range for your data source you can pick up all data that is newer than =TODAY()-90 or you could OFFSET 90 cells if the dates are not contiguous.

 

[al_b_cnu]

Re: Source Data Formula needed for rolling 90 days entries C

Hi,

How about ...

Assuming that you have a heading in row 1 of 'Training Log',
Create a 'Chart data' sheet.

In A2:A91 put the formula
=OFFSET('Training Log'!A1,COUNTA('Training Log'!A:A)-91,0)

In B2:B91 put the formula
=OFFSET('Training Log'!C1,COUNTA('Training Log'!C:C)-91,0)

In C2:C91 put the formula
=OFFSET('Training Log'!E1,COUNTA('Training Log'!E:E)-91,0)*1440

and just use A2:C91 for your chart.

HTH

Alan

 

[zoso]

Re: Source Data Formula needed for rolling 90 days entries C

Alan

Thanks for your help, but I was wondering if a solution for this would be possible without a 'data sheet' which is what I have at the moment?

TIA!

 

[inarbeth]

Re: Source Data Formula needed for rolling 90 days entries C

Alan

Thanks for your help, but I was wondering if a solution for this would be possible without a 'data sheet' which is what I have at the moment?

TIA!

It doesn't matter whether your data is on the same sheet or a separate sheet. If you post back with some more information we can help.

 

[zoso]

Inarbeth

I don't know what else I can say to help you help me, as it's all at the top of this page - please tell me what else you need to know and I'll BRB!

 

[PaddyD]

there are a number of examples of dynamic charting here:

www.tushar-mehta.com

...under the dynamic chart tutorial. As far as the 'plot decimal values for seconds' etc is concerned, you will have to manipulate the source data appropriately & chart of that.

 

[zoso]

Re: Source Data Formula needed for rolling 90 days entries C

thanks for the link!