Expression Result is different then final Number in Sheet

[Drew_Cook]

Hi,

I have a formula that when I test gives me the right number but the number that is then outputted in the form is different and wrong.

Any Ideas on why this would be different?

[Steve]

Please post screenshots of the following:

• The complete expression.

• The output of the test showing the correct result.

• The form view showing the incorrect result.

[Marc_Dillon]

Steve:

• The output of the test showing the correct result.
• The form view showing the incorrect result.

Can you give a fuller picture of these 2 things? Can’t really tell much from the 2 screenshots you posted before.

How do you know the correct answer is 72? At this point I don’t believe anyone except for you can troubleshoot whether this expression is producing the correct result or not.

And did you have any comments or responses to the 3 items I mentioned?

[Drew_Cook]

CEILING((0.65*([King]*MAX(Prices[K Set Weight])+([Queen]+[Queen Pullout])*MAX(Prices[Q Set Weight])+([Full]+[Full Pullout])*MAX(Prices[F Set Weight])+[Twin]*MAX(Prices[T Set Weight])+[Sleep Total]*1.5*MAX(Prices[Bath Weight])+[Sleep Total]*(MAX(Prices[Wash Weight])+MAX(Prices[Hand Weight]))+ MAX(Prices[Mats Weight])*[BA]+[Kitchen]*MAX(Prices[Dish Towel Weight]))+0.65*(MAX(Prices[Cases Weight ])*([King]*3.5+([Queen]+[Queen Pullout]+[Full]+[Full Pullout])*2.5+[Twin])+([King]+[Queen]+[Full]+[Full Pullout])*MAX(Prices[King Top Sheet Weight]))+[Twin]*MAX(Prices[Twin Blanket Weight]),1))

[Drew_Cook]

Any Idea why this would happen?

[1minManager]

Is this a virtual column?

[Drew_Cook]

It is not

[1minManager]

Ok, your going to have to split up the formula into bite sized chunk and test each bit

[Marc_Dillon]

I attempted to re-format and fix your expression. With proper formatting you can more easily spot errors, as well as allowing other people to read your giant expression without their brain exploding.

Couple things:

1. When you write (x)(y), are you meaning (x) times (y)? I made the multiplication explicit, as you should.
2. What’s with the “,1” at the end? I don’t see you using any expression that use a 2nd argument, I deleted it.
3. You’re not trying to use the column’s value in the expression for that column are you (circular expression) ?

---

CEILING(

  0.65  *
  (
    [King] * MAX( Prices[K Set Weight] )
    +
    ([Queen]+[Queen Pullout]) * MAX(Prices[Q Set Weight])
    +
    ([Full]+[Full Pullout]) * MAX(Prices[F Set Weight])
    +
    [Twin] * MAX(Prices[T Set Weight])
    +
    [Sleep Total] * 1.5 * MAX(Prices[Bath Weight])
    +
    [Sleep Total] * ( MAX(Prices[Wash Weight]) + MAX(Prices[Hand Weight]) )
    + 
    MAX(Prices[Mats Weight]) * [BA]
    +
    [Kitchen] * MAX(Prices[Dish Towel Weight])
  )

  +

  0.65 * 
  (
    MAX(Prices[Cases Weight ]) 
    * 
    (
      3.5 * [King] 
      +
      2.5 * ( [Queen] + [Queen Pullout] + [Full] + [Full Pullout] )
      +
      [Twin]
    )
    +
    [King] 
    + 
    [Queen] 
    + 
    [Full] 
    + 
    [Full Pullout]

    *
    MAX(Prices[King Top Sheet Weight])
  )
  
  +
  
  [Twin] * MAX(Prices[Twin Blanket Weight])

)

[Drew_Cook]

Marc_Dillon:

CEILING(

  0.65  *
  (
    [King] * MAX( Prices[K Set Weight] )
    +
    ([Queen]+[Queen Pullout]) * MAX(Prices[Q Set Weight])
    +
    ([Full]+[Full Pullout]) * MAX(Prices[F Set Weight])
    +
    [Twin] * MAX(Prices[T Set Weight])
    +
    [Sleep Total] * 1.5 * MAX(Prices[Bath Weight])
    +
    [Sleep Total] * ( MAX(Prices[Wash Weight]) + MAX(Prices[Hand Weight]) )
    + 
    MAX(Prices[Mats Weight]) * [BA]
    +
    [Kitchen] * MAX(Prices[Dish Towel Weight])
  )

  +

  0.65 * 
  (
    MAX(Prices[Cases Weight ]) 
    * 
    (
      3.5 * [King] 
      +
      2.5 * ( [Queen] + [Queen Pullout] + [Full] + [Full Pullout] )
      +
      [Twin]
    )
    +
    [King] 
  

With this My Total is 59 for my test for your formula when it should be 72 my expression gives me 72 but it writes the number as 51 in the google sheet.