Invalid data type

Vince Barnes

Hi 

 

I hope someone can help with a very frustrating problem. I have a sheet which is used to calculate work progressed. It takes the number of checked items in a column and sums them using the COUNTIF formula "Actual formula is ="" + COUNTIF([240mm Term]5:[240mm Term]171, 1)"

 

The cell underneath this take this value and subtracts it from the value in the cell above this and divides by the cell above, thus giving the percentage complete. however, this formula returns and "Invalid Type" Error. I have even tried basic subtraction and this returns the same error

=[240mm Term]173 - [240mm Term]174

 

Can anyone shed any light as to why I am getting this error? Is it because the COUNTIF isnt returning a number? If so how can I convert the result tof COUNTIF to a number?

 

many thanks

 

Vince

Vince Barnes

I should also add that the column has a conditional format so when the cell is checked it turns from Red to Green and it is a checkbox type cell

 

Travis

Hi Vince, the formula result is formatted as a Text value because of the "" + you included at the front of the formula. 

 

Two options: remove the "" +  Is there a reason you included this? If you are not concatenating then it is unnecessary.

 

Another option is to format the result as a number in your second formula. Like this:

 

=VALUE([240mm Term]173) - [240mm Term]174

 

VALUE() will turn a text value into a number (assuming its only digits and not letters).