Avoiding #INVALID DATA TYPE when cells are blank

SteCoxy

Hello,

I am setting up a staffing leave Smartsheet for my team and unfortunately I am getting the #INVALID DATA TYPE error message.

There are 2 formulae set up within the sheet:

Leave Remaining (the dark grey row) is calculated as follows: =[Leave Allowance] - Taken + [Leave Bought] + Carried + [Long Service]

Taken (the dark grey row) is calculated as follows: =SUMIFS(Taken2:Taken6, [Leave Type]2:[Leave Type]6, "Annual Leave") - There are other types of leave but for this instance I only want it to calculate annual leave.

I realise the error message is coming up as there is no data in the start date/end date on row 3 however, how can I get round this. In Excel, it would allow me to have no data in and it would fill the Taken column with a value of "0". 

Is it a different formula I need to apply? Any help would be much appreciated!

L_123

What you are talking about is the iferror formula:

=iferror([Leave Allowance]@row- Taken@row + [Leave Bought]@row+ Carried@row + [Long Service]@row),0)

you can switch the 0 to "" if you want to make it blank if there is an error. 

That said, If error is not what you need. If the cell is blank, your formula should treat it as a 0. That said your leave remaining formula is referencing taken1 which has an error. taken1 is referencing taken3 which has an error. That means that taken3 is causing both the other errors. if you delete what is in cell taken3 the errors should go away.

Also, you need to add row references to your leave remaining formula. you can either do @row, or just put a 1 there, but it won't reference anything without a row reference.

 

SteCoxy

Thank you kindly for taking the time to reply to my query.

I've got to be 100% honest I am a total novice with formulas in spreadsheets, so I'm slightly confused by your response sorry! Any chance you could explain in layman's terms?

Ideally what I am wishing for is the cell in "Taken" column to either be blank or show a zero until I enter dates in the "Start Date" and "End Date" columns but at present when I try and apply the formula in this cell the error message is coming up, which then affects the dark grey row for both "Leave Remaining" and "Taken".

 

Mike Wilday

All of those errors are being created by the formula in Taken3. What formula are you using in Taken3?

Try using this formula in row 3 of the taken column.

•  =IFERROR(Networkdays(Start@row, Finish@row),"") 

SteCoxy

In Taken3 I am using the following formula: =NETWORKDAYS([Start Date]3, [End Date]3)

Mike Wilday

Try plugging the formula I shared. the @row replaces the row number and searches the row you have the formula in. Plug that formula in and see if it works for you. =IFERROR(Networkdays(Start@row, Finish@row),"") 

That formula will leave the cell blank when there isn't a start or a finish date. 

Amy Shank

I believed I've fixed the formula to correct the @cell invalid data type error and instead it returns a blank cell. However, I've used the following formulas to try and get the average from a column in my spreadsheet but I keep getting an invalid data type error in the sheet summary formula..

=AVG([LE Total Days]:[LE Total Days])

=AVG(COLLECT([LE Total Days]:[LE Total Days]), ISNUMBER,(@cell)))