Formula Help

[Joneye]

Cells M & N have numbers in, is there a formula that I can use that will show these cells as a 0. The only reason they are like this is G is blank. somtimes Column G has a date in sometimes not, if not id like a 0 or a blank cell. Is this possible?


<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">completion_date</td><td style=";">expected_completion_date</td><td style=";">action_date</td><td style=";">fin_year</td><td style=";">period</td><td style=";">week_no</td><td style=";">Days</td><td style=";">Weeks</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15/11/2010</td><td style="text-align: right;;">2011</td><td style="text-align: right;;">5</td><td style="text-align: right;;">21</td><td style="text-align: right;;">-28926</td><td style="text-align: right;;">-5785.2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">=NETWORKDAYS(<font color="Blue">I2,G2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">=M2/5</td></tr></tbody></table></td></tr></table><br />

 

[truskawa]

Hi,
Have you tried something like this:

=IF(ISBLANK(G2),"0",.....)
in .... enter formula you whish to be calculated if G2 is not empty

 

[VoG]

Try in M2

=IF(G2="",0,NETWORKDAYS(I2,G2))

 

[Joneye]

VOG - perfect your formula works well.

Truskawa - I like the look of your formula, im using excel 2003 can not seem to get it working.

 

[truskawa]

VOG - perfect your formula works well.

Truskawa - I like the look of your formula, im using excel 2003 can not seem to get it working.

Hi,

I'm using excel 2003 as well
I understand that networkdays is function you wrote yourself?
it should work

M2:
=IF(ISBLANK(G2),0,NETWORKDAYS(I2,G2))
N2:
=IF(ISBLANK(G2),0,M2/5)

 

[Joneye]

Yep got it in one now, many thanks I like the "isblank" area im thinking this can be used in other areas, many thanks.