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]

Sure, try this:
=if(SUM(F10-$F$7)=-60.35,"",(SUM(F10-$F$7))) HTH, Slink

 

[MattBell]

That is superb. Very impressive, thank you very much.

My wife is astounded.

 

[Gerald Higgins]

Remember also, the SUM in your formula is redundant, and you can simplify to

=if((F10-$F$7)=-60.35,"",F10-$F$7)

And instead of the above, I would do

=if(F10="","",F10-$F$7)

This means that, if, in the future, you decide to change your benchmark, then you don't need to change all your formulas.

 

[MattBell]

Thats great thank you.

I've just spotted another problem though. I'm not working in the 'time' format therefore excel is treating a whole number (a minute in this case) as having 100 seconds in it rather than 60 seconds in it. So if I enter a benchmark time of 60.35 and then an actual time of 61.00 it tells me that I'm 65 seconds slower rather than 25 seconds slower.

How do I change the cells to understand its time data and to keep the formulas in place?

Thanks in advance

Matt

 

[Gerald Higgins]

The last formula I posted will work, whatever number format you are using, whether it's time or normal numbers.
I would suggest converting to time format.
If you have a time of 60min 35sec, enter this as 0:60:35.
Excel may well automatically format this for you as a time.
If it doesn't, format it yourself, using an appropriate time format.

 

[Gerald Higgins]

Actually, thinking about it, if you input the time as 0:60:35, it will probably display it as 1:00:35, because it's just over an hour. If you really want to show it as 60:35, use a custom format of [m]:ss.

 

[MattBell]

Thanks, thats solved the time problem, however it's only working if I enter a slower actual time in the results cells.

For example if I enter 01:00:45 then excel gives me 00:00:10 depicting 10 seconds slower than a 01:00:35 benchmark but if I enter anything lower than the benchmark amount (for example 01:00:25) then i just see a cell full of '###'

Any ideas?

 

[SlinkRN]

Hi again Matt,
Welcome to the board by the way - you can learn SO much here, I sure did!!!
If you format everything to [m]:ss by going to format, custom, and then type [m]:ss
Then you can use this formula:
=IF(F10<=$F$7,$F$7-F10,F10 -$F$7)
The problem with this is that it isn't obvious whether you are above or below your time. I'm still trying to figure out how to make it negative if you are under your time. HTH, Slink
PS: Oh, by the way, you have to type the numbers in as 1:00:35 but with that format it will then show up as 60:35

 

[SlinkRN]

Oops, forgot about the check to see if it was blank - use this instead:
=IF(F10>=$F$7,F10-$F$7,IF(F10<>"",($F$7-F10),""))