+ Reply to Thread
Results 1 to 12 of 12

.0 is creating #VALUE! Error

  1. #1
    kidcasey13
    Guest

    .0 is creating #VALUE! Error

    I am currently using the following formula:

    =(H21*60)-IF(MID(H21,FIND(".",H21,1),5)>0,(MID(H21,FIND(".",H21,1),5)*60),"")+(MID(H21,FIND(".",H21,1),5))

    However, I get the results of #VALUE!

    In cell H21, the value is 27.00

    How do I force excel to recognize the .00 in the formula?

    Thanks,
    Ryan


  2. #2
    Scoops
    Guest

    Re: .0 is creating #VALUE! Error

    Hi Ryan

    FIND is a string function, it's looking for text.

    What result are you after?

    Regards

    Steve


  3. #3
    Duke Carey
    Guest

    RE: .0 is creating #VALUE! Error

    Are you trying to multiply the integer portion by 60 and add to that the
    decimal portion?

    =60*INT(H21)+MOD(H21,1)


    "kidcasey13" wrote:

    > I am currently using the following formula:
    >
    > =(H21*60)-IF(MID(H21,FIND(".",H21,1),5)>0,(MID(H21,FIND(".",H21,1),5)*60),"")+(MID(H21,FIND(".",H21,1),5))
    >
    > However, I get the results of #VALUE!
    >
    > In cell H21, the value is 27.00
    >
    > How do I force excel to recognize the .00 in the formula?
    >
    > Thanks,
    > Ryan
    >
    >


  4. #4
    Peo Sjoblom
    Guest

    Re: .0 is creating #VALUE! Error

    What are you trying to do? Since you are multiplying with 60 I assume this
    has something to do with times
    If you have a decimal value for hours like 12.5 equaling 12 hours and 30
    minutes you can convert it to excel time by multiplying with 24 and format
    as time. If you have 12.5 and it means 12 hours and 50 minutes use INT(H21)
    to extract hours and MOD(H21,1)*100 to extract minutes

    --

    Regards,

    Peo Sjoblom

    Northwest Excel Solutions

    www.nwexcelsolutions.com

    (remove ^^ from email address)

    Portland, Oregon




    "kidcasey13" <[email protected]> wrote in message
    news:[email protected]...
    >I am currently using the following formula:
    >
    > =(H21*60)-IF(MID(H21,FIND(".",H21,1),5)>0,(MID(H21,FIND(".",H21,1),5)*60),"")+(MID(H21,FIND(".",H21,1),5))
    >
    > However, I get the results of #VALUE!
    >
    > In cell H21, the value is 27.00
    >
    > How do I force excel to recognize the .00 in the formula?
    >
    > Thanks,
    > Ryan
    >



  5. #5
    Niek Otten
    Guest

    Re: .0 is creating #VALUE! Error

    Hi Ryan,

    instead of "H21", use "TEXT(H21,"0.00")"

    --
    Kind regards,

    Niek Otten

    "kidcasey13" <[email protected]> wrote in message news:[email protected]...
    >I am currently using the following formula:
    >
    > =(H21*60)-IF(MID(H21,FIND(".",H21,1),5)>0,(MID(H21,FIND(".",H21,1),5)*60),"")+(MID(H21,FIND(".",H21,1),5))
    >
    > However, I get the results of #VALUE!
    >
    > In cell H21, the value is 27.00
    >
    > How do I force excel to recognize the .00 in the formula?
    >
    > Thanks,
    > Ryan
    >




  6. #6
    Bob Phillips
    Guest

    Re: .0 is creating #VALUE! Error

    Is this what you need?

    =(H21*60)-IF(ISERROR(MID(H21,FIND(".",H21,1),5)>0),0,(MID(H21,FIND(".",H21,1
    ),5)*60))*2

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "kidcasey13" <[email protected]> wrote in message
    news:[email protected]...
    > I am currently using the following formula:
    >
    >

    =(H21*60)-IF(MID(H21,FIND(".",H21,1),5)>0,(MID(H21,FIND(".",H21,1),5)*60),""
    )+(MID(H21,FIND(".",H21,1),5))
    >
    > However, I get the results of #VALUE!
    >
    > In cell H21, the value is 27.00
    >
    > How do I force excel to recognize the .00 in the formula?
    >
    > Thanks,
    > Ryan
    >




  7. #7
    kidcasey13
    Guest

    Re: .0 is creating #VALUE! Error

    I am definitely working with times here. We work in quarter hours, so
    ..00, .25, .50, .75 (0 mins, 15 mins, 30 mins, 45 mins)

    Avg hrs worked/week = 27.00

    So I have 27.00 hours worked this week, however, I need to convert it
    into minutes for a different formula (to determine how much data entry
    is necessary per minute), therefore I'm taking the 27*60 to get the
    number of minutes from hours, and adding the number of minutes to the
    hours. So, 27*60=1620+Minutes (in this case, zero, which is throwing
    the error).

    In another example, 27.15=1620+20=1640 minutes.


  8. #8
    Bob Phillips
    Guest

    Re: .0 is creating #VALUE! Error

    So why not just

    =H21*60


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "kidcasey13" <[email protected]> wrote in message
    news:[email protected]...
    > I am definitely working with times here. We work in quarter hours, so
    > .00, .25, .50, .75 (0 mins, 15 mins, 30 mins, 45 mins)
    >
    > Avg hrs worked/week = 27.00
    >
    > So I have 27.00 hours worked this week, however, I need to convert it
    > into minutes for a different formula (to determine how much data entry
    > is necessary per minute), therefore I'm taking the 27*60 to get the
    > number of minutes from hours, and adding the number of minutes to the
    > hours. So, 27*60=1620+Minutes (in this case, zero, which is throwing
    > the error).
    >
    > In another example, 27.15=1620+20=1640 minutes.
    >




  9. #9
    Scoops
    Guest

    Re: .0 is creating #VALUE! Error

    Hi kidcasey13

    Are you absolutely sure?

    27.15 hours = 1620+9 = 1629 minutes

    But that's not a quarter hour as you've stated you are using, nor is 20
    minutes a quarter hour. What you'd expect to see for quarter hours is:

    0.0 = 0
    0.25 = 15
    0.5 = 30
    0.75 = 45

    Bob's H21*60 seems eminently sensible and sufficient to me even if your
    time is a text entry.

    Regards

    Steve


  10. #10
    kidcasey13
    Guest

    Re: .0 is creating #VALUE! Error

    27.00*60 = 1620
    27.25*60 = 1635

    Understandably it is only a difference of 15 "minutes", however that is
    still not accurate. (I realized I had a typing error above and put .15
    instead of .25)

    I have even rethought part of my formula, to this:

    =(INT(H147)*60)+(MID(H138,FIND(".",H147,1),5))

    the INT works great, however because excel doesn't recognize the ".00",
    it's giving me #VALUE!

    Ultimately, I'd like my formula to recognize that if there is a .25,
    ..50 or .75 to calculate the above way; but if not, to calculate just by
    =INT(H147*60); I've tried using the "IF" & "MID" together, but it's not
    recognizing the ".00"

    Still looking for help...


  11. #11
    Scoops
    Guest

    Re: .0 is creating #VALUE! Error

    Hi kidcasey13

    I fail to understand your problem - why is 15 minutes not accurate for
    a quarter hour?

    Perhaps you should post the contents of your source cell and what you
    expect to see in the calculated cell; using your formula 27.15 (text or
    value) returns 1620.15 for me.

    Regards

    Steve


  12. #12
    Scoops
    Guest

    Re: .0 is creating #VALUE! Error

    Hi kidcasey13

    I'm not understanding - why is 15 minutes not a quarter hour? I fail
    to see how it could be any more accurate than that.

    Using your formula above, 27.25 (whether text or value) returns 1620.25
    for me, which I read as 1620 minutes and 15 seconds. Perhaps you
    should post the contents of your source cell (27.25?) and what you
    expect to see in your calculated cell.

    Regards

    Steve


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1