How would I make this formula return value of "n/a" if nothing to calculate?

[Aimee S.]

Currently the value returned if there is no data in AW to calculate is #DIV/0!.

How would I add to this formula the criteria that if there are no values to average, return value "n/a"?

=AVERAGE(IF($K$3:$K$5000=1,IF($C$3:$C$5000="CAP - Backhaul",IF(TEXT($AG$3:$AG$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"),IF($AW$3:$AW$5000="Under",1,IF($AW$3:$AW$5000="Over",0))))))

 

[Krishnakumar]

Hi,

May be..

=LOOKUP(9.9999E+307,CHOOSE({1,2},NA(),AVERAGE(IF($K$3:$K$5000=1,IF($C$3:$C$5000="CAP - Backhaul",IF(TEXT($AG$3:$AG$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"),IF($AW$3:$AW$5000="Under",1,IF($AW$3:$AW$5000="Over",0))))))))

 

[Aimee S.]

It says when trying that formula you provided:

"The specified formula you entered cannot be entered because it uses more levels of nesting than are allowed in the current file format"

I use Excel 2010

File format is .xls for backwards compatibility.

 

[T. Valko]

Currently the value returned if there is no data in AW to calculate is #DIV/0!.

How would I add to this formula the criteria that if there are no values to average, return value "n/a"?

=AVERAGE(IF($K$3:$K$5000=1,IF($C$3:$C$5000="CAP - Backhaul",IF(TEXT($AG$3:$AG$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"),IF($AW$3:$AW$5000="Under",1,IF($AW$3:$AW$5000="Over",0))))))

If you're using Excel 2007 or later...

=IFERROR(your formula here,"N/A")

You do want the TEXT string N/A and not the #N/A error, right?

 

[Aimee S.]

Correct, the text sting n/a. Lemme try this.

 

[Aimee S.]

I tried this:

=IFERROR(AVERAGE(IF('3-FactoryCalculation'!$K$3:'3-FactoryCalculation'!$K$5000=1,IF('3-FactoryCalculation'!$C$3:'3-FactoryCalculation'!$C$5000="Base",IF(TEXT('3-FactoryCalculation'!$AG$3:'3-FactoryCalculation'!$AG$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"),IF('3-FactoryCalculation'!$AW$3:'3-FactoryCalculation'!$AW$5000="Under",1,IF('3-FactoryCalculation'!$AW$3:'3-FactoryCalculation'!$AW$5000="Over",0)))))),"n/a")

And just got back a different percentage than the right one.

 

[T. Valko]

I use Excel 2010

File format is .xls for backwards compatibility.

Correct, the text sting n/a. Lemme try this.

Ok, well, you won't be able to use the IFERROR version then.

 

[Aimee S.]

Agghh I got it. Forgot the CTRL SHIFT ENTER confirmation part hehehehe.

Thanks!!!!