Make a field null using IF function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lisaspb@gmail.com - 19 Jun 2006 17:06 GMT

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 this Message

CLR - 19 Jun 2006 18:09 GMT

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
[quoted text clipped - 3 lines]
> I'd like my formulat to work something like this:
> =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))

Reply to this Message

lisaspb@gmail.com - 19 Jun 2006 19:10 GMT

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,
[quoted text clipped - 15 lines]
> > I'd like my formulat to work something like this:
> > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))

Reply to this Message

CLR - 19 Jun 2006 19:20 GMT

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
[quoted text clipped - 20 lines]
> > > I'd like my formulat to work something like this:
> > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))

Reply to this Message

Arvi Laanemets - 19 Jun 2006 19:06 GMT

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

Reply to this Message

lisaspb@gmail.com - 19 Jun 2006 19:23 GMT

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
>
[quoted text clipped - 13 lines]
> > I'd like my formulat to work something like this:
> > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))

Reply to this Message

Dave Peterson - 20 Jun 2006 02:08 GMT

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
[quoted text clipped - 17 lines]
> > > I'd like my formulat to work something like this:
> > > =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7))

Signature

Dave Peterson

Reply to this Message

Arvi Laanemets - 20 Jun 2006 07:02 GMT

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

Reply to this Message