Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveCopyAs with a twist | Excel Discussion (Misc queries) | |||
duplicates with a twist | Excel Discussion (Misc queries) | |||
Match with a Twist | Excel Worksheet Functions | |||
Vlookup With A Twist | Excel Worksheet Functions | |||
Large() with twist | Excel Worksheet Functions |