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

[Dan the Man]

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

 

[PCLIVE]

Try this:

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

HTH,
Paul

 

[T. Valko]

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

 

[Dan the Man]

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

 

[Dan the Man]

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

 

[Bob Phillips]

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

--
---
HTH

Bob

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

 

[T. Valko]

Not following you on that one, Bob.

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

 

[Harlan Grove]

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.

 

[Dan the Man]

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

 

[Dan the Man]

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