1

I'm trying to use the MIN and SUM function but the answer always comes back with either 0, ERROR or !DIV/0.

The format I want to have for my times are for example: 0.03, 10.56, 1:13.00.

=MIN example:

enter image description here

=SUM example:

enter image description here

One final thing I would want is that the last digit can only be 0, 3 or 6. So for example 1.03 + 1.06 + 1.03 = 3.13 and not 3.12.

Please help, I've been trying to solve this for days.

  • Sorry, it is mm:ss.ms – user207079 Jan 20 '19 at 21:06
  • you getting 0, ERROR or !DIV/0 because you are trying to calculate text format with number format. its same like if you would try to sum ten+10 and expect 20 – user0 Jan 20 '19 at 22:31
0
  • first, you need to do is to change your input by entering it like 0:0:10.13 which will output as 00:10.13 if you will follow steps from image and custom format your column:

1


  • then you can sum it up with formula =SUM(A8+B10+D7):

1


  • lastly, to cover the last digit change you can use simple IF() to convert it based on logic rules:
    =IF(AND(VALUE(RIGHT(C13, 1))>0, VALUE(RIGHT(C13, 1))<3),  LEFT(C13,7)&0,
     IF(AND(VALUE(RIGHT(C13, 1))>3, VALUE(RIGHT(C13, 1))<6),  LEFT(C13,7)&3,
     IF(AND(VALUE(RIGHT(C13, 1))>6, VALUE(RIGHT(C13, 1))<10), LEFT(C13,7)&6, C13)))

2

| improve this answer | |
  • I applied the new time format "Minute (01) : Second (01) . Milliseconds (01)" to column A. Wrote the times in A1 to A3. gave A4 a function of "=SUM(A1+A2+A3)" and I get: i.gyazo.com/5d3163cf893e1ff0b5e6e1cdc97c30a0.png – user207079 Jan 20 '19 at 22:48
  • If I change the ":" into a "." in both the cells and in the format it all works. lol – user207079 Jan 20 '19 at 23:04
  • Think I got it to work now. was a bit confused about your first sentence about typing one thing and it will display in another. Thank you so much <3 – user207079 Jan 20 '19 at 23:07
  • @user207079 docs.google.com/spreadsheets/d/… – user0 Jan 20 '19 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.