If Statement Error

  • Thread starter Thread starter Norma
  • Start date Start date
N

Norma

Please take a look at the following formula and let me
know why I am getting a result of DIV/0:

=IF(AVERAGE('[IRM Feb04 financials.xls]12 Month Trend'!
$B$467:$C$467)/1000=0,0,(AVERAGE('[IRM Feb04
financials.xls]12 Month Trend'!$B$467:$C$467)/1000))
 
Hi
try
=IF(ISERROR(AVERAGE('[IRM Feb04 financials.xls]12 Month
Trend'!$B$467:$C$467)/1000),0,(AVERAGE('[IRM Feb04 financials.xls]12
Month Trend'!$B$467:$C$467)/1000))
 
Because you likelu have a #DIV0 error in your source range of data, ie '[IRM
Feb04 financials.xls]12 Month Trend'!$B$467:$C$467

Check that out first, and if you have then fix it there. If you don't see any
errors then I'm guessing that that range is possibly completely empty which will
also return that error to the AVERAGE function, and as such will flow through to
any formula that use that function on an empty range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Norma said:
Please take a look at the following formula and let me
know why I am getting a result of DIV/0:

=IF(AVERAGE('[IRM Feb04 financials.xls]12 Month Trend'!
$B$467:$C$467)/1000=0,0,(AVERAGE('[IRM Feb04
financials.xls]12 Month Trend'!$B$467:$C$467)/1000))
Click to expand...
 
Frank, thank you so very much. It works perfect.

Have an awesome evening!
-----Original Message-----
Hi
try
=IF(ISERROR(AVERAGE('[IRM Feb04 financials.xls]12 Month
Trend'!$B$467:$C$467)/1000),0,(AVERAGE('[IRM Feb04 financials.xls]12
Month Trend'!$B$467:$C$467)/1000))

--
Regards
Frank Kabel
Frankfurt, Germany
Please take a look at the following formula and let me
know why I am getting a result of DIV/0:

=IF(AVERAGE('[IRM Feb04 financials.xls]12 Month Trend'!
$B$467:$C$467)/1000=0,0,(AVERAGE('[IRM Feb04
financials.xls]12 Month Trend'!$B$467:$C$467)/1000))
Click to expand...

.
Click to expand...
 
Thanks for looking at it. However, the reply from Frank
took care of the issue.

Thanks again!

-----Original Message-----
Because you likelu have a #DIV0 error in your source range of data, ie '[IRM
Feb04 financials.xls]12 Month Trend'!$B$467:$C$467

Check that out first, and if you have then fix it there. If you don't see any
errors then I'm guessing that that range is possibly completely empty which will
also return that error to the AVERAGE function, and as such will flow through to
any formula that use that function on an empty range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------- -------------------



Norma said:
Please take a look at the following formula and let me
know why I am getting a result of DIV/0:

=IF(AVERAGE('[IRM Feb04 financials.xls]12 Month Trend'!
$B$467:$C$467)/1000=0,0,(AVERAGE('[IRM Feb04
financials.xls]12 Month Trend'!$B$467:$C$467)/1000))
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004


.
Click to expand...
 
LOL - It usually does :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Norma said:
Thanks for looking at it. However, the reply from Frank
took care of the issue.

Thanks again!

-----Original Message-----
Because you likelu have a #DIV0 error in your source range of data, ie '[IRM
Feb04 financials.xls]12 Month Trend'!$B$467:$C$467

Check that out first, and if you have then fix it there. If you don't see any
errors then I'm guessing that that range is possibly completely empty which will
also return that error to the AVERAGE function, and as such will flow through to
any formula that use that function on an empty range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------- -------------------



Norma said:
Please take a look at the following formula and let me
know why I am getting a result of DIV/0:

=IF(AVERAGE('[IRM Feb04 financials.xls]12 Month Trend'!
$B$467:$C$467)/1000=0,0,(AVERAGE('[IRM Feb04
financials.xls]12 Month Trend'!$B$467:$C$467)/1000))
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004


.
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Taking Average ignoring #DIV/0! in the range... 1
Pivot Table column to column measurement 1
Average of time spent monthly getting DIV/0 error 2
If Formula 2
Calculated field with IIf makes field text rather than percentage 1
SUMPRODUCT 1
Average formula only works partially..... 6
Using Sumproduct when some of the values are null 5

Back
Top