Make a field null using IF function

[lisaspb]

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))

 

[CLR]

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

 

[Arvi Laanemets]

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]

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

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

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))

 

[CLR]

Is this what you mean......

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

hth
Vaya con Dios,
Chuck, CABGx3

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

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

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))

 

[lisaspb]

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.

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

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]

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.

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.

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

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))

 

[Arvi Laanemets]

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

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.

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

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))