Why is a simple division formula not working?

Christopher Kline

I am trying to divide two different cells into another cell.

I am taking my information from my quantity column (qty3) and my rate column (rate3) and divide them.

I want it to look like this =QTY3/RATE3 but it always comes up #INVALID OPERATION.

I also changed the symbol to addition and it did not add the numbers correctly, so I do not know what to do.

Paul Newcome

If you remove the quotes from around each of the numbers in your IF statement, it will output numerical values. Then you should be able to do a straight division of the two cells.

Mark Cronk

Good evening,. Are you column names QTY and RATE? If so, your formula should work. The column with the formula needs to be text/number.

If your column names are QTY3 AND RATE3 then your formula is =QTY3@row/RATE3@row

Help?

Mark

Paul Newcome

@Christopher Kline Did @Mark Cronk's suggestion not help? If not, are you able to provide a screenshot of the data within the sheet to include column headers? Exactly how are the numbers populated in each of the columns?

Christopher Kline

@Paul Newcome It helped us troubleshoot the cause. The column is being populated by an IF statement so we now understand the hiccup of why it wasn't working. We ended up taking the IF statement and putting it into the "division result" column and modifying each IF statement . I'm trying to figure out FUNCTIONS and it seems like this is not the most efficient way to go about it. I'm guessing we need a VALUE function somewhere? Any help or guidance is appreciated. And if I use a VALUE function does that need to be in its own column or can I put it into a formula?

Here's what we are doing spelled out:

QTY/Rate = "X" which is our "Hours Req" Column (The QTY column is a number we manually enter - The "Rate" is being pulled from that columns IF statement)

We ended up doing: QTY/=IF(Product3 = "SP", (QTY3 / 105),........plus a whole bunch of others.

I'm wondering how we bypass this formula and tell smartsheet that the result in "Rate" is the number to use?

Paul Newcome

Try this...

=QTY3@row/VALUE(RATE3@row)

It could also be an adjustment to your existing IF statement. Exactly what is that IF formula that is populating the Rate column?

Christopher Kline

I'm not getting that to work. Attached are screen shots of your question and what I plugged in. This is a copy of the file I am working in so the "Product" in the previous formula is now "EDIT(1)"

The above gives me "UNPARSEABLE"

The above is the IF statement in "Rate" (below for clarity)

=IF([EDIT (1)]3 = "SP", "105", IF([EDIT (1)]3 = "MP", "74", IF([EDIT (1)]3 = "LP", "59", IF([EDIT (1)]3 = "ST", "36", IF([EDIT (1)]3 = "MT", "55", IF([EDIT (1)]3 = "LT", "37", IF([EDIT (1)]3 = "ORN", "82", IF([EDIT (1)]3 = "14x24", "70", IF([EDIT (1)]3 = "12X20", "62", IF([EDIT (1)]3 = "VS", "38", IF([EDIT (1)]3 = "12x18", "80", IF([EDIT (1)]3 = "11x11", "70", IF([EDIT (1)]3 = "FT", "18", IF([EDIT (1)]3 = "WT", "12", IF([EDIT (1)]3 = "18x18", "66", IF([EDIT (1)]3 = "SRWT", "INFO", IF([EDIT (1)]3 = "LSP", "65")))))))))))))))))

Paul Newcome

If you remove the quotes from around each of the numbers in your IF statement, it will output numerical values. Then you should be able to do a straight division of the two cells.

Christopher Kline

I got it to work by removing the quotes, but I also had to remove the "@3" from the formula. Below is what is in the cell now. Really appreciate the help on this one!!! Thank you!

Paul Newcome

My apologies. I missed the "@". If you removed the quotes then you shouldn't need the VALUE. If you use the VALUE, then you shouldn't have to remove the quotes. Both accomplish the same thing.

Christopher Kline

Ok, Got both ways to work!

Paul Newcome

Great. Happy to help. 👍️