+ Reply to Thread
Results 1 to 8 of 8

Make a field null using IF function

  1. #1

    Make a field null using IF function

    Is there a function that will make a field Null, or change the format
    of a field from time to general? I'm calculating time differences for
    which some fields do not have a time. I also need an average of these
    results, but the fields without a time are returning 0:00 (because the
    field format is HH:MM) which are causing the averages to miscalculate.
    I'd like my formulat to work something like this:
    =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))


  2. #2
    CLR
    Guest

    RE: Make a field null using IF function

    You cannot change the format of one cell from a function in another cell. If
    you wish to average a column of numbers excluding those whose value is 00:00,
    then ..

    =SUM(A:A)/COUNTIF(A:A,"<>00:00")

    Summing should prove no problem because 00:00 does not contribute to the sum.

    hth
    Vaya con Dios,
    Chuck, CABGx3



    "[email protected]" wrote:

    > Is there a function that will make a field Null, or change the format
    > of a field from time to general? I'm calculating time differences for
    > which some fields do not have a time. I also need an average of these
    > results, but the fields without a time are returning 0:00 (because the
    > field format is HH:MM) which are causing the averages to miscalculate.
    > I'd like my formulat to work something like this:
    > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    >
    >


  3. #3
    Arvi Laanemets
    Guest

    Re: Make a field null using IF function

    Hi

    =IF(OR(D7="",F7=""),"",F7-D7)

    NB! It is a formula, and it doesn't have anything to do with formats.
    Functions SUM, COUNT, and AVERAGE ignore empty strings - to be more exact,
    they ignore all strings.


    Arvi Laanemets


    <[email protected]> wrote in message
    news:[email protected]...
    > Is there a function that will make a field Null, or change the format
    > of a field from time to general? I'm calculating time differences for
    > which some fields do not have a time. I also need an average of these
    > results, but the fields without a time are returning 0:00 (because the
    > field format is HH:MM) which are causing the averages to miscalculate.
    > I'd like my formulat to work something like this:
    > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    >




  4. #4

    Re: Make a field null using IF function

    This won't work because there are instances where there is a valid
    calculation result of 00:00 in which case they need to be included for
    the average calculation. Is there a way to have the "IF" calculation
    return Null???

    CLR wrote:
    > You cannot change the format of one cell from a function in another cell. If
    > you wish to average a column of numbers excluding those whose value is 00:00,
    > then ..
    >
    > =SUM(A:A)/COUNTIF(A:A,"<>00:00")
    >
    > Summing should prove no problem because 00:00 does not contribute to the sum.
    >
    > hth
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > Is there a function that will make a field Null, or change the format
    > > of a field from time to general? I'm calculating time differences for
    > > which some fields do not have a time. I also need an average of these
    > > results, but the fields without a time are returning 0:00 (because the
    > > field format is HH:MM) which are causing the averages to miscalculate.
    > > I'd like my formulat to work something like this:
    > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    > >
    > >



  5. #5
    CLR
    Guest

    Re: Make a field null using IF function

    Is this what you mean......

    =IF(OR(ISBLANK(D7),ISBLANK(F7)),"",F7-D7)

    hth
    Vaya con Dios,
    Chuck, CABGx3



    "[email protected]" wrote:

    > This won't work because there are instances where there is a valid
    > calculation result of 00:00 in which case they need to be included for
    > the average calculation. Is there a way to have the "IF" calculation
    > return Null???
    >
    > CLR wrote:
    > > You cannot change the format of one cell from a function in another cell. If
    > > you wish to average a column of numbers excluding those whose value is 00:00,
    > > then ..
    > >
    > > =SUM(A:A)/COUNTIF(A:A,"<>00:00")
    > >
    > > Summing should prove no problem because 00:00 does not contribute to the sum.
    > >
    > > hth
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "[email protected]" wrote:
    > >
    > > > Is there a function that will make a field Null, or change the format
    > > > of a field from time to general? I'm calculating time differences for
    > > > which some fields do not have a time. I also need an average of these
    > > > results, but the fields without a time are returning 0:00 (because the
    > > > field format is HH:MM) which are causing the averages to miscalculate.
    > > > I'd like my formulat to work something like this:
    > > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    > > >
    > > >

    >
    >


  6. #6

    Re: Make a field null using IF function

    This will not work b/c the supposedly "" (empty) fields are still being
    counted by the CountA function which is the original problem I'm
    having.


    Arvi Laanemets wrote:
    > Hi
    >
    > =IF(OR(D7="",F7=""),"",F7-D7)
    >
    > NB! It is a formula, and it doesn't have anything to do with formats.
    > Functions SUM, COUNT, and AVERAGE ignore empty strings - to be more exact,
    > they ignore all strings.
    >
    >
    > Arvi Laanemets
    >
    >
    > <[email protected]> wrote in message
    > news:[email protected]...
    > > Is there a function that will make a field Null, or change the format
    > > of a field from time to general? I'm calculating time differences for
    > > which some fields do not have a time. I also need an average of these
    > > results, but the fields without a time are returning 0:00 (because the
    > > field format is HH:MM) which are causing the averages to miscalculate.
    > > I'd like my formulat to work something like this:
    > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    > >



  7. #7
    Dave Peterson
    Guest

    Re: Make a field null using IF function

    Excel can return an empty string (""), but it can't return an empty cell.

    Maybe you could use a different function than =counta()

    =sumproduct(--(a1:a10<>""))

    You can't use the whole column when you do this, though.

    If that doesn't work for you, you may want to post your formula that uses the
    =counta() function.

    [email protected] wrote:
    >
    > This will not work b/c the supposedly "" (empty) fields are still being
    > counted by the CountA function which is the original problem I'm
    > having.
    >
    > Arvi Laanemets wrote:
    > > Hi
    > >
    > > =IF(OR(D7="",F7=""),"",F7-D7)
    > >
    > > NB! It is a formula, and it doesn't have anything to do with formats.
    > > Functions SUM, COUNT, and AVERAGE ignore empty strings - to be more exact,
    > > they ignore all strings.
    > >
    > >
    > > Arvi Laanemets
    > >
    > >
    > > <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Is there a function that will make a field Null, or change the format
    > > > of a field from time to general? I'm calculating time differences for
    > > > which some fields do not have a time. I also need an average of these
    > > > results, but the fields without a time are returning 0:00 (because the
    > > > field format is HH:MM) which are causing the averages to miscalculate.
    > > > I'd like my formulat to work something like this:
    > > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    > > >


    --

    Dave Peterson

  8. #8
    Arvi Laanemets
    Guest

    Re: Make a field null using IF function

    Hi

    Because you used substraction in your formula, I decided the returned result
    must be a number, not a string. When results, you want to count, are numbers
    only, use COUNT instead of COUNTA.

    Arvi Laanemets


    <[email protected]> wrote in message
    news:[email protected]...
    > This will not work b/c the supposedly "" (empty) fields are still being
    > counted by the CountA function which is the original problem I'm
    > having.
    >
    >
    > Arvi Laanemets wrote:
    > > Hi
    > >
    > > =IF(OR(D7="",F7=""),"",F7-D7)
    > >
    > > NB! It is a formula, and it doesn't have anything to do with formats.
    > > Functions SUM, COUNT, and AVERAGE ignore empty strings - to be more

    exact,
    > > they ignore all strings.
    > >
    > >
    > > Arvi Laanemets
    > >
    > >
    > > <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Is there a function that will make a field Null, or change the format
    > > > of a field from time to general? I'm calculating time differences for
    > > > which some fields do not have a time. I also need an average of these
    > > > results, but the fields without a time are returning 0:00 (because the
    > > > field format is HH:MM) which are causing the averages to miscalculate.
    > > > I'd like my formulat to work something like this:
    > > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))
    > > >

    >




+ 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