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

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

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

Try this:

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

HTH,
Paul

"Dan the Man" wrote in message
...
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

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

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

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
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

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

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

"PCLIVE" wrote:

Try this:

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

HTH,
Paul

"Dan the Man" wrote in message
...
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

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

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
"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
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

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

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)



"Dan the Man" wrote in message
...
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
"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
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

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

Not following you on that one, Bob.

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

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
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)



"Dan the Man" wrote in message
...
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
"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
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

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

"Bob Phillips" 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.

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

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



"Harlan Grove" wrote:

"Bob Phillips" 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.

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

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


"Dan the Man" wrote:

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



"Harlan Grove" wrote:

"Bob Phillips" 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.