Do not calculate non numerical values

[clintonmadden]

Complicated Formula - ISERROR function

Hi,

I have been working on a spreadsheet to calculate the total number of staff when given the number of staff on annual leave and the percentage of staff on leave for 2 subsections of total staff for each department but there can be occasions when the data entered is non numerical or 0 and I'm not able to calculate it. I was wondering if anyone would know how I could take this into account in my forumlas.

thanks very much

Clinton

 

[namliam]

Use if and the ISNA or ISERROR function

You can use IF and > 0 to check for the 0 value(s) to prevent the DIV/0 error

 

[clintonmadden]

Thanks for the help, I'll have a look at this later on today

 

[clintonmadden]

OK I'm having a few problems putting the formula together. I wasn't sure how to use the ifna option so i was wondering if i could use the iferror function like this:

From this =(D6+F6)/((D6/C6)+(F6/E6))

i get
=IFERROR((D6+F6)/(IFERROR(D6/C6),0,(D6/C6))+(IFERROR(F6/E6),0,(F6/E6))),C6,(D6+F6)/(IFERROR(D6/C6),0,(D6/C6))+(IFERROR(F6/E6),0,(F6/E6))

so if either red or purple returns an error it'll display a 0 and if there is an error in D6+F6 divided by red+purple it'll return the figure in C6 and if there's no error it'll run the whole expression. Sorry if I'm explaining it wrong, just thought it'd be easier like this. I have tried this formula but I get an error saying I've entered too few arguements and it highlights the first instance of C6 in the red highlighted part of the formula.

Any help would be appreciated.

I might have to start a new thread seeing as it's slightly changed, but I'll see if I can get any help here first.

Thanks very much
Clinton

 

[namliam]

iSerror, not iFerror....

IF(ISERROR(Yourcalc),0,YourCalc)