ExcelBanter - Averaging without #DIV0!

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Averaging without #DIV0! (https://www.excelbanter.com/excel-worksheet-functions/264733-averaging-without-div0.html)

malcolm

Averaging without #DIV0!

Im 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. Im 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 dont 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

Noodnutt @ Work

Averaging without #DIV0!

G'day Malcolm

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

HTH
Mark

"Malcolm" wrote in message
...
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

Ken Hudson

Averaging without #DIV0!

Hi Malcolm,

One way...

=IF(ISERROR(AVERAGE(J28/C27)),"",J28/C27)

Ken Hudson

"Malcolm" wrote:

Im 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. Im 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 dont 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

Brad

Averaging without #DIV0!

=if(c27=0,"",AVERAGE(J28/C27))
--
Wag more, bark less

"Malcolm" wrote:

Im 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. Im 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 dont 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

Ziggy

Averaging without #DIV0!

On May 27, 3:33*pm, "Noodnutt @ Work" wrote:
G'day Malcolm

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

HTH
Mark

"Malcolm" wrote in message

...

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

Ziggy

Averaging without #DIV0!

On May 27, 4:06*pm, Ziggy wrote:
On May 27, 3:33*pm, "Noodnutt @ Work" wrote:

G'day Malcolm

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

HTH
Mark

"Malcolm" wrote in message

...

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)

FSt1

Averaging without #DIV0!

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:

Im 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. Im 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 dont 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

Noodnutt @ Work

Averaging without #DIV0!

D'oh

Seeing the trees through the forest..

Thx Ziggy

"Ziggy" wrote in message
...
On May 27, 3:33 pm, "Noodnutt @ Work" wrote:
G'day Malcolm

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

HTH
Mark

"Malcolm" wrote in message

...

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

Fred Smith[_4_]

Averaging without #DIV0!

Or, more simply:
=if(c27=0,"",j28/c27)

Regards,
Fred

"Brad" wrote in message
...
=if(c27=0,"",AVERAGE(J28/C27))
--
Wag more, bark less

"Malcolm" wrote:

Im 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. Im 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 dont 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

Max

Averaging without #DIV0!

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:
Im 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. Im 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 dont 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

malcolm

Averaging without #DIV0!

FSt1, Hi, again,
Once again, your on the money and your solution worked slick.
Thank you and best regards,

Malcolm

"FSt1" wrote:

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:

Im 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. Im 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 dont 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

malcolm

Averaging without #DIV0!

"Ziggy" wrote:

On May 27, 3:33 pm, "Noodnutt @ Work" wrote:
G'day Malcolm

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

HTH
Mark

"Malcolm" wrote in message

...

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

Sig, Hi,
Since I am using 2007, IFERROR does work great.
Thanks,
Malcolm
.

bala_vb

Quote:

Originally Posted by Malcolm (Post 956584)

if you are using excel 2007, refer iserror function. it will throw empty "" when error generates.

iserror(AVERAGE(J28/C27),"",AVERAGE(J28/C27)).

all the best

All times are GMT +1. The time now is 11:50 PM.