From: FSt1 on
hi
vary your formula a tad
=IF(c27=0,0,average(j28/c27))
thought i haven't seen the average formula used quite that way.
but if c27 is zero then the above formual will put a zero in the cell
instead of #DIV/0

regards
FSt1

"Malcolm" wrote:

> I'm using a worksheet that has a cell (C27) that totals rooms and a cell
> (J28) that totals rates and a cell (J29) that is the average daily rate
> total, rate/total rooms. I'm using the formula =AVERAGE(J28/C27). The only
> problem is until I actually input a number into C27 I get the #DIV/0!
> displayed in my ADR cell (J29). I don't always need this data so is there a
> way I can input the formula and not have the #DIV/0! in cell J29?
> Thanks,
> Malcolm
>
From: Ziggy on
On May 27, 4:06 pm, Ziggy <ziggy...(a)xmission.com> wrote:
> On May 27, 3:33 pm, "Noodnutt @ Work" <ma...(a)camerons.com.au> wrote:
>
>
>
>
>
> > G'day Malcolm
>
> > =IF($J28=0,0,AVERAGE(J28/C27))
>
> > HTH
> > Mark
>
> > "Malcolm" <Malc...(a)discussions.microsoft.com> wrote in message
>
> >news:B9AACD7C-6185-43B7-A52F-DAEF11B769E1(a)microsoft.com...
>
> > > I'm using a worksheet that has a cell (C27) that totals rooms and a cell
> > > (J28) that totals rates and a cell (J29) that is the average daily rate
> > > total, rate/total rooms. I'm using the formula =AVERAGE(J28/C27). The only
> > > problem is until I actually input a number into C27 I get the #DIV/0!
> > > displayed in my ADR cell (J29). I don't always need this data so is there
> > > a
> > > way I can input the formula and not have the #DIV/0! in cell J29?
> > > Thanks,
> > > Malcolm- Hide quoted text -
>
> > - Show quoted text -
>
> It think when you divide you already creat the average. You don't need
> the AVERAGE function.
>
>                 =IF(C27=0,0,J28/C27)
>
>                =IF(iserror(J28/C27),0,J28/C27)
>
> in 2007;   =IFERROR(J28.C27,0)
>
> My preference runs to the if error formulas
>
> Sig- Hide quoted text -
>
> - Show quoted text -

Should have been =IFERROR(J28/C27,0)
From: Noodnutt on
D'oh

Seeing the trees through the forest..

Thx Ziggy


"Ziggy" <ziggy943(a)xmission.com> wrote in message
news:187e7e66-0e53-4b4b-97dc-3ba712b646da(a)t34g2000prd.googlegroups.com...
On May 27, 3:33 pm, "Noodnutt @ Work" <ma...(a)camerons.com.au> wrote:
> G'day Malcolm
>
> =IF($J28=0,0,AVERAGE(J28/C27))
>
> HTH
> Mark
>
> "Malcolm" <Malc...(a)discussions.microsoft.com> wrote in message
>
> news:B9AACD7C-6185-43B7-A52F-DAEF11B769E1(a)microsoft.com...
>
>
>
> > I'm using a worksheet that has a cell (C27) that totals rooms and a cell
> > (J28) that totals rates and a cell (J29) that is the average daily rate
> > total, rate/total rooms. I'm using the formula =AVERAGE(J28/C27). The
> > only
> > problem is until I actually input a number into C27 I get the #DIV/0!
> > displayed in my ADR cell (J29). I don't always need this data so is
> > there
> > a
> > way I can input the formula and not have the #DIV/0! in cell J29?
> > Thanks,
> > Malcolm- Hide quoted text -
>
> - Show quoted text -

It think when you divide you already creat the average. You don't need
the AVERAGE function.

=IF(C27=0,0,J28/C27)

=IF(iserror(J28/C27),0,J28/C27)

in 2007; =IFERROR(J28.C27,0)

My preference runs to the if error formulas

Sig



From: Fred Smith on
Or, more simply:
=if(c27=0,"",j28/c27)

Regards,
Fred


"Brad" <Brad(a)discussions.microsoft.com> wrote in message
news:F0D5DAF0-77CF-4CD8-AEB3-2038DE572865(a)microsoft.com...
> =if(c27=0,"",AVERAGE(J28/C27))
> --
> Wag more, bark less
>
>
> "Malcolm" wrote:
>
>> I'm using a worksheet that has a cell (C27) that totals rooms and a cell
>> (J28) that totals rates and a cell (J29) that is the average daily rate
>> total, rate/total rooms. I'm using the formula =AVERAGE(J28/C27). The
>> only
>> problem is until I actually input a number into C27 I get the #DIV/0!
>> displayed in my ADR cell (J29). I don't always need this data so is there
>> a
>> way I can input the formula and not have the #DIV/0! in cell J29?
>> Thanks,
>> Malcolm
>>

From: Max on
Think you don't need to use "average", and this simple IF trap which checks
the denominator cell should suffice:
=IF(C27="","",J29/C27)
--
Max
Singapore
---
"Malcolm" wrote:
> I'm using a worksheet that has a cell (C27) that totals rooms and a cell
> (J28) that totals rates and a cell (J29) that is the average daily rate
> total, rate/total rooms. I'm using the formula =AVERAGE(J28/C27). The only
> problem is until I actually input a number into C27 I get the #DIV/0!
> displayed in my ADR cell (J29). I don't always need this data so is there a
> way I can input the formula and not have the #DIV/0! in cell J29?
> Thanks,
> Malcolm
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Date inconsistent help please :)
Next: Date Help