Can I make #N/A show as 0?

[CharlieBeth]

I want to be able to total these figures (from a HLookup), but clearly where there is no amount (because that person was not working that day) I get #N/A which means my total also reads #N/A

<table style="width: 367px; height: 57px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 66pt;" width="88"> <col style="width: 35pt;" width="47"> <col style="width: 45pt;" width="60"> <col style="width: 53pt;" width="70"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; width: 66pt;" height="17" width="88">Staff</td> <td class="xl26" style="width: 35pt;" width="47">Day 1</td> <td class="xl26" style="width: 45pt;" width="60">Day 2</td> <td class="xl26" style="width: 53pt;" width="70">Sum</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Emma C</td> <td class="xl25">£39.93</td> <td class="xl25">£27.73</td> <td class="xl25">£67.66</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Elaine S</td> <td class="xl25">£27.73</td> <td class="xl25">#N/A</td> <td class="xl25">#N/A</td> </tr> </tbody></table>
I need the #N/A's to read 0 so I can still total all the days.

Can it be done?

 

[VoG]

Try like this

=IF(ISNA(your formula),0,your formula)

 

[CharlieBeth]

Try like this

=IF(ISNA(your formula),0,your formula)

It doesn't like it, tells me there is an error.

 

[VoG]

What is your formula?

 

[CharlieBeth]

=hlookup(a18,$a$2:$dd$15,14,false)

 

[VoG]

Try

=IF(ISNA(HLOOKUP(A18,$A$2:$DD$15,14,FALSE)),0,HLOOKUP(A18,$A$2:$DD$15,14,FALSE))

 

[CharlieBeth]

Ohh! Thank you SO much! That works!