Using sheet summary fields within sheet formulas

George Pritchard

Hi, I have a problem where I am trying to use smart sheet to generate the sell price of products based on a fixed % margin. Using a margin that is fixed is simple, I just put the value into the column formula. However, if I want to adjust the margin, I had hoped to use the sheet summary to do this.

I have used sheet summary fields before in formulas, adding together various values, and it worked. However every time I try and use any kind of mathematic equation that is not a simple addition, the formula spits out "INVALID OPERATION".

Example:

=1 + Margin# works

=1 - Margin# invalid

=BUY@row * (1 - Margin#) invalid

Any ideas of other workarounds?

Andrée Starå

Hi @George Pritchard

I hope you're well and safe!

It should work!

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

George Pritchard

Bizarrely, it has started to work. I rang a colleague to get an opinion and before the call it was failing, but when I tried to show them it started functioning!

Andrée Starå

@George Pritchard

Excellent!

Glad you got it working! A glitch in the matrix! 😉

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

George Pritchard

I have found the glitch, it is * I think * to do with the column properties of the sheet formula field.

https://www.dropbox.com/s/k2hrapjo9uu5cvv/Screen%20Recording%202022-10-06%20at%203.37.04%20pm.mov?dl=0

You can see on this when I try it with different fields what was happening, but when I "Clear Field" and type again, even if I have previously copied the value that did work, it works?! The field property on all is set the same.

Just a glitch as you say.