#DIV/0! Error-Another Twist, assistance please?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan the Man - 20 Jul 2007 21:14 GMT

Don helped me with a formula I was struggling with, and the two he developed
work PERFECTLY:

=AVERAGE(IF('Quarter 1 Data'!$G$4:$G$500="Allen",'Quarter 1
Data'!$C$4:$C$500))

=AVERAGE(IF('Quarter 1 Data'!$F$4:$F$500="OP",'Quarter 1 Data'!$C$4:$C$500))

I have one additional question however, and I'll use the following as an
example to describe what I am looking for:

If there is no input data yet for Row C or Row G, I would understadably see:
#DIV/0! in the cell referencing the first formula above (which I do). I
realize that as soon as I input any data into Rows C or G, the #DIV/0! error
will be replaced with the actual numeric data generated by the formula. This
would also be true of the second formula in the absence of any data in Rows C
or F.

Is there any way of using an IF statement to keep the cells blank, until the
relevant data is input. It's probably more of a cosmetic issue, but I just
hate seeing that "Division" error sign (even if the only assoicated error is
the lack of data in the specific cells requiring data).

Thanks!

Dan

Reply to this Message

PCLIVE - 20 Jul 2007 21:20 GMT

Try this:

=if(C1+G1=0,"",YourFormula)

HTH,
Paul

> Don helped me with a formula I was struggling with, and the two he
> developed
[quoted text clipped - 30 lines]
>
> Dan

Reply to this Message

Dan the Man - 20 Jul 2007 21:38 GMT

That didn't seem to do it! I should also include that I have formula for Rows
C,F and G filled between Columns 4:500. I was also wondering if the ISERROR
statement could figure in somewhere to help the problem. I just hate seeing
#DIV/0! especially when I know it is only there due to the absence of data!

Dan

> Try this:
>
[quoted text clipped - 37 lines]
> >
> > Dan

Reply to this Message

T. Valko - 20 Jul 2007 21:31 GMT

One way:

=IF(COUNTIF('Quarter 1 Data'!$G$4:$G$500,"Allen"),AVERAGE(IF('Quarter 1
Data'!$G$4:$G$500="Allen",'Quarter 1 Data'!$C$4:$C$500)),"")

Signature

Biff
Microsoft Excel MVP

> Don helped me with a formula I was struggling with, and the two he
> developed
[quoted text clipped - 30 lines]
>
> Dan

Reply to this Message

Dan the Man - 20 Jul 2007 21:46 GMT

That almost worked. The cell went blank with your formula (taking away the
#DIV/0! error), but when I entered the relevant data, intstead of providing
the outcome, I now get the #REF! error (I tested the formula by putting Allen
info into it, and the appropriate dates). Getting closer, YES!

Dan

> One way:
>
[quoted text clipped - 35 lines]
> >
> > Dan

Reply to this Message

Bob Phillips - 20 Jul 2007 22:06 GMT

Worked for me Dan, as given.

As an aside, it can be sllightly shortened

=IF(COUNTIF('Quarter 1 Data'!$G$4:$G$500,"Allen"),
AVERAGE(IF('Quarter 1 Data'!$G$4:$G$500="Allen",'Quarter 1
Data'!$C$4:$C$500)))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> That almost worked. The cell went blank with your formula (taking away the
> #DIV/0! error), but when I entered the relevant data, intstead of
[quoted text clipped - 48 lines]
>> >
>> > Dan

Reply to this Message

T. Valko - 20 Jul 2007 22:19 GMT

Not following you on that one, Bob.

If "Allen" doesn't exist then the result will be FALSE.

Signature

Biff
Microsoft Excel MVP

> Worked for me Dan, as given.
>
[quoted text clipped - 60 lines]
>>> >
>>> > Dan

Reply to this Message

Harlan Grove - 20 Jul 2007 22:39 GMT

"Bob Phillips" <bob....@somewhere.com> wrote...
...

>As an aside, it can be sllightly shortened
>
>=IF(COUNTIF('Quarter 1 Data'!$G$4:$G$500,"Allen"),
>AVERAGE(IF('Quarter 1 Data'!$G$4:$G$500="Allen",
>'Quarter 1 Data'!$C$4:$C$500)))

...

Only if you want to see FALSE when there are no instances of Allen in
the G range.

Reply to this Message

Dan the Man - 20 Jul 2007 23:14 GMT

I'm getting further along. The formula below works per Harlan's suggestion,
and removes occurrences of the #DIV/0! error. It now places FALSE in the cell
when no instances of Allen exisit:

=IF(COUNTIF('Quarter 1 Data'!$G$4:$G$500,"Allen"),AVERAGE(IF('Quarter 1
Data'!$G$4:$G$500="Allen",'Quarter 1 Data'!$C$4:$C$500)))

As opposed to FALSE showing up when there are no instances of Allen in the G
Range, can the cell just stay blank? That would be ideal?

In addition, I'd like to be able to do the same thing with the second
formula to avoid the #DIV/0! error. Below is an example of the current
formula which works when data is input into the F Range, but leaves the
#DIV/0! error when it is not:

=AVERAGE(IF('Quarter 1 Data'!$F$4:$F$500="ARC",'Quarter 1 Data'!$C$4:$C$500))

Thanks,

Dan

> "Bob Phillips" <bob....@somewhere.com> wrote...
> ....
[quoted text clipped - 7 lines]
> Only if you want to see FALSE when there are no instances of Allen in
> the G range.

Reply to this Message

Dan the Man - 20 Jul 2007 23:48 GMT

I got it:

=IF(COUNTIF('Quarter 1 Data'!$G$4:$G$500,"Courtney"),AVERAGE(IF('Quarter 1
Data'!$G$4:$G$500="Courtney",'Quarter 1 Data'!$C$4:$C$500)),"")

=IF(COUNTIF('Quarter 1 Data'!$F$4:$F$500,"TP"),AVERAGE(IF('Quarter 1
Data'!$F$4:$F$500="TP",'Quarter 1 Data'!$C$4:$C$500)),"")

Thanks everyone for the combined help. I do have another #DIV/0! error
question, but I'll post in a new message to close this thread!

Dan

> I'm getting further along. The formula below works per Harlan's suggestion,
> and removes occurrences of the #DIV/0! error. It now places FALSE in the cell
[quoted text clipped - 28 lines]
> > Only if you want to see FALSE when there are no instances of Allen in
> > the G range.

Reply to this Message