Can formula's remain dormant and only display when you enter data?

[MattBell]

Hi,

I've created a very simple running spreadsheet to keep track of my Marathon training. I have a column with a benchmark time within it - Cell F7 (time of 60.35 mins) - this relates to my fastest 10k time so far.

Below this i have rows where i intend to enter the time of my latest run for that distance, (F11-F176)

To the right of this column I'd like to display wether my latest run was quicker or slower than the benchmark and by how much - as follows:

F7: 60.35 (Benchmark)
F10: 60.05 (Latest run)
G10:=SUM(F10-$F$7) displaying -0.30

I have protected cell F7 and copied the formula down to the cells beneath it.

My question is, is there anyway of avoiding the as yet unused cells from displaying '-60.35' ? I'd like them to have the formula in the background but not actually show anything until I enter a value into the 'F' column.

I hope that makes sense?

Thanks in advance,

Matt Removed e-mail address - Moderator

 

[SlinkRN]

OK, now you can use conditional formatting to make the cell green when you beat your benchmark time. On the home tab (Excel 2007 or 2010), click on Conditional Formatting (after highlighting the results cells) and then click on "New Rule" and then "Use a formula to determine which cells to format". Now in the box under "Format values where this formula is true:" type =(F10<>"")*(F10<$F$7) and then click on the "format" button on that same screen and choose a color and say OK and then OK again. Now, just use the format painter brush (also on the home tab) to copy that cell format down as far as you want.
Don't forget that you always need to type the time in the format 0:45:15 or 1:23:13 etc. I'm sure the MVPs here can fix that for you but as long as it works maybe you'll be OK with that? I've tested all of this and it seems to do the trick! Let me know if it works for you!

 

[MattBell]

Hi,

Sorry for the late reply, i'm in the UK so we are on different time zones.

Thanks very much i've made those changes and it all works very nicely. I especially like the green text when I improve a time - very motivating. I've increased the columns and done the same layout for different distances to include 5K, 10K, Half Marathon, and 23 miles.

One last thing - how would I go about creating some graphs or charts that can link to the data and show in graphical form how I'm improving? It would be nice if I could have a series of graphs on separate tabs with 'time' on the vertical axis and 'Date' along the bottom and then points showing on the chart for each date eventually resulting in a nice curve that depicts my improving times.

This may be a step too far so apologies if it is!

For info my columns are as follows:

Column B10 downwards - Date
Cell F7 - Benchmark time
Column F10 downwards - Actual time
Column G10 downwards - +/- time (based on actual - benchmark)

Thanks in advance,

Matt

 

[Gerald Higgins]

A chart should be straightforward.
I think you want to use col B as the x axis data, and col F as the chart series data.
You might also want to create a column that repeats the benchmark time as a straight line, so you could use a formula like this

=$F&7

copied all the way down some column, and use that column to drive a second series.

Depending on which version of Excel you are using, there are various semi-automatic ways of creating charts. Try playing around with Insert, Chart, and it should prompt you through the various steps.

 

[SlinkRN]

Good morning Matt!
Glad to help! My son is an ironman and is going to do the ironman again next year - I'll have to share this idea with him! I have to go to work today so I won't be able to look at this until tonight but I agree with Gerald that you should play around with the insert chart tool. You can assign a certain range to the chart and as the data updates the charts should update. Have a great day! Slink

 

[SlinkRN]

Hi Matt,
It looks like charts are not going to be easy with all of these formatted cells! You might want to start a new thread asking how to make a chart when your cells are formatted as times. I am out of my league when it comes to charts, I can only do the basics there. Slink

 

[Gerald Higgins]

Formatting of the cells won't affect charts based on the data.
I do charts based on time data all the time.
Give it a go, and post back if you have problems.