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

MattBell

New Member
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?

Matt Removed e-mail address - Moderator

Last edited by a moderator:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sure, try this:

Last edited:
That is superb. Very impressive, thank you very much.

My wife is astounded.

Remember also, the SUM in your formula is redundant, and you can simplify to
Code:
``=if((F10-\$F\$7)=-60.35,"",F10-\$F\$7)``

And instead of the above, I would do
Code:
``=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.

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?

Matt

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.

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.

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?

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

Last edited:
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),""))

Replies
3
Views
912
Replies
1
Views
178
Replies
7
Views
852
Replies
10
Views
939
Replies
3
Views
14K

1,203,396
Messages
6,055,158
Members
444,766
Latest member
bryandaniel5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back