LARGE Formula giving #DIV/0!

[PLN]

I have a spreadsheet containing the daily sales. I have a
section at the bottom in which I track the top 10 daily
averages, I update this manually. (this is because I track
the week and how many days in the week next to it which is
not information on the top)

I want to use the LARGE formula [e.g. =LARGE
($H$3:$H$172,3)] next to it to double check that my manual
tracking is accurate. The problem is I have the average
formula copied in column H and if I don't have the daily
sales yet it shows #DIV/0! in the cell. When I use the
LARGE formula, it also gives me the #DIV/0!.

Is there a way to have Excel ignore any cells that have
that div error and give me the information on the top 10
sales without me removing those cells from the LARGE range?

Thanks.
I am using Excel 2002, not sure if that matters.

 

[Bob Phillips]

Why not trap the #DIV/0 error in the average, something like

=IF(ISERROR(your_average_formula),0,your_average_formula)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Why not just fix your formulas returning #DIV/0 so that they return 0 instead.
Just trap for whatever the denominator is being 0 or blank, and if this is the
case return 0.

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

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

I have a spreadsheet containing the daily sales. I have a
section at the bottom in which I track the top 10 daily
averages, I update this manually. (this is because I track
the week and how many days in the week next to it which is
not information on the top)

I want to use the LARGE formula [e.g. =LARGE
($H$3:$H$172,3)] next to it to double check that my manual
tracking is accurate. The problem is I have the average
formula copied in column H and if I don't have the daily
sales yet it shows #DIV/0! in the cell. When I use the
LARGE formula, it also gives me the #DIV/0!.

Is there a way to have Excel ignore any cells that have
that div error and give me the information on the top 10
sales without me removing those cells from the LARGE range?

Thanks.
I am using Excel 2002, not sure if that matters.

 

[Popandopulo]

Hi,

Since you update the file on daily basis, you may like
using dynamic ranges
Good example at
http://www.ozgrid.com/Excel/DynamicRanges.htm

Having a dynamic range, you won't need to create blank
rows in advance.

Regards,
Puzatiy Popandopulo

 

[PLN]

Thank you both, that simple formula did the trick!

-----Original Message-----
Why not trap the #DIV/0 error in the average, something like

=IF(ISERROR(your_average_formula),0,your_average_formula)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a spreadsheet containing the daily sales. I have a
section at the bottom in which I track the top 10 daily
averages, I update this manually. (this is because I track
the week and how many days in the week next to it which is
not information on the top)

I want to use the LARGE formula [e.g. =LARGE
($H$3:$H$172,3)] next to it to double check that my manual
tracking is accurate. The problem is I have the average
formula copied in column H and if I don't have the daily
sales yet it shows #DIV/0! in the cell. When I use the
LARGE formula, it also gives me the #DIV/0!.

Is there a way to have Excel ignore any cells that have
that div error and give me the information on the top 10
sales without me removing those cells from the LARGE range?

Thanks.
I am using Excel 2002, not sure if that matters.

.