# Make a field null using IF function

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))  Register To Reply

2. ## 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

"lisaspb@gmail.com" 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))
>
>  Register To Reply

3. ## 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

<lisaspb@gmail.com> wrote in message
> 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))
>  Register To Reply

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
>
>
>
> "lisaspb@gmail.com" 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))
> >
> >  Register To Reply

5. ## 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

"lisaspb@gmail.com" 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
> >
> >
> >
> > "lisaspb@gmail.com" 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))
> > >
> > >

>
>  Register To Reply

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
>
>
> <lisaspb@gmail.com> wrote in message
> > 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))
> >  Register To Reply

7. ## 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.

lisaspb@gmail.com 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
> >
> >
> > <lisaspb@gmail.com> wrote in message
> > > 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  Register To Reply

8. ## 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

<lisaspb@gmail.com> wrote in message
> 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
> >
> >
> > <lisaspb@gmail.com> wrote in message
> > > 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))
> > >

>  Register To Reply