#INVALID OPERATION when dividing

Joshua Peacock · 05/02/19

I'm trying to do a count of how many staff have uploaded a particular document. I have who has uploaded the document and how many staff in total, and I want to display that as a percentage on the sheet. Whenever I try to divide # of Staff Uploaded by Total Staff I get #INVALID OPERATION. I've tried it both in formula and just pluging in the numbers manually but it keeps happening. Here's what I was trying to use:

=([Staff Uploaded]10 / [Total Staff]1)

And then format the result as a percentage.

Is that correct?

L_123 · 05/02/19

sounds like a data type issue. can you post an image of your sheet and the formulas you use to count for the 2 values you are dividing?

Joshua Peacock · 05/02/19

The formula I'm using for the others are:

To get total staff:

=COUNT([Staff Name]1:[Staff Name]63)

and to count how many have uploaded:

=COUNTIF([Uploaded Document]1:[Uploaded Document]63, 1) + ""

Now the column with the COUNTIF is a checkbox since it is just a yes or no, but I tried using the divide formula in a text column and that didn't work either.

Andrée Starå · 05/02/19

Hi Joshua,

You're getting the error because the uploaded document result is formatted as text and not a number.

Remove the +"" and it should work.

=COUNTIF([Uploaded Document]1:[Uploaded Document]63, 1) + ""

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Joshua Peacock · 05/02/19

Ah ok, the reason I formatted it as a text was because when I don't use "" the Uploaded Document cell then becomes #BOOLEAN EXPECTED. Is there a way to get around this?

Andrée Starå · 05/03/19

This will probably help!

#BOOLEAN EXPECTED

Cause

The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.

Resolution

Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example =TODAY() + "" will allow you to enter today's date into a text column.

Let me know if you need help!

Best,

Andrée

Paul Newcome · 05/03/19

Because you are converting to text to get around the BOOLEN EXPECTED error, you will need to convert it back into a usable number for the formula. Try something like this...

=VALUE([Staff Uploaded]10) / [Total Staff]1

Using the VALUE function will convert the text string that represents a number into an actual number that can be used in formulas.

#INVALID OPERATION when dividing

Comments

Cause

Resolution

Help Article Resources

Categories