Sum formula Error When References Results from IF Formula

Gladys Liley

I am getting an #Invalid Operation error when my Sum formula references a cell with results from an IF formula:

=SUM([Column 8]1 * [Column 9]1) results as #Invalid Operation error

 

Column 9 formula

=IF([Column 2] = "A", "0.5%", IF([Column 2] = "B", "1.5%"))

 

I've tried to fix by using a nested VALUE= but that does not work either. Is there anything I can do to fix it?

Thank you greatly for any help.

Gladys

Mike Wilday

Hi, Gladys, based on the asterisk in your formula it appears that you are trying to multiply the two fields together. You should be able to accomplish what you are looking to do using either of these formulas: 

=[Column8]1 * [Column9]1 

If you're looking to add them together use this formula,

=SUM([Column 8]1, [Column 9]1)

Here is more information about the sum formula:  https://help.smartsheet.com/function/sum

J. Craig Williams

Gladys,

Mike is correct (I believe). The #INVALID OPERATION is due to the multiplication.

In addition

VALUE("1.5%") will return #INVALID VALUE

The VALUE() function will return the numeric value of text IF IT CAN. But since % is not a number, it can not in this case.

For your formula in column 9, return the number and then format it for percentage (you may need to add a decimal to the standard percentage format)

=IF([Column 2] = "A", 0.005, IF([Column 2] = "B", 0.015))

For follow on formulas, be aware that 1% is 0.01 value.

Lastly, if you are trying to perform a SUMPRODUCT type function, see this post:

https://community.smartsheet.com/discussion/weighted-average-sumproduct

I hope this helps.

Craig

 

 

Gladys Liley

Craig & Mike,

It worked!

I removed the SUM from the formula, removed the parenthesis ("") from the Column 9 formula around the % value and changed the column type to %...wallla.  You guys are best!

Thank you again!

J. Craig Williams

You are welcome.

Craig

Dan Davis

Gladys,

Good catch Mike and Craig, sometimes it's hard to see the obvious.  

I would offer a friendly suggestion with the IF formula as you are using it ...  

End the formula with instructions if the entry in column 2 is not A or B.  Below I added a dash in parenthesis, or you could use "error", or "Enter A or B in Column 2", or any other message.  Having the formula put something in the cell and not just let it be blank helps to not mistake the cell as a blank cell and accidentally overwriting the formula.

I was actually surprised to see that the IF formula worked without this final instruction.

=IF([Column 2] = "A", 0.005, IF([Column 2] = "B", 0.015,"-"))

Have fun.

Dan Davis

J. Craig Williams

Good point Dan.

Every such decision must be weighed against the in-system design.

For example, if [Column 2] is a drop down list, then an error is on the input, not the output, so I would do something else to find and fix that (the recent data validation changes for example)

As the original post says, there was a formula somewhere that was summing the results column. That means blank or "-" would be OK, but something else like

=COUNTIF(Results:Results, VALUE(@cell) > 0)

would toss an error.

Also, because of the SUM() after, the flag for missed entry might be overlooked as it is one step removed from the cause.

There are plenty of guidelines but fewer hard and fast rules.

When answering questions, I try to focus on the requirements, sometimes asking questions if a requirement seems to be missing.

Craig

 

Mike Wilday

Good point Craig. I agree with your statement about trying to focus on the requirements. Sometimes that's all that's needed. Sometimes the output then reveals further questions. I tend to address those when they come up, unless there are clear signs of other issues that could arise - then I bring attention to those right away.