0

Ok I have a table on a worksheet which has some columns for some calculations

PPS | Qty | TtlPrice | Comm | Fee | Ttl | format

Column Definitions

  • PPS - straight Numeric(9,5)
  • Qty - straight Numeric(12,0)
  • ttlPrice - PPS * Qty
  • Comm - Standard flat rate
  • Fee - =IF([@PPS] > 1, -SUM(0.005 * [@Qty]), 0)
  • Ttl - =SUM([@TtlPrice]:[@Fee])
  • format - Conditional formatting control column

Problem is that when I evaluate the Fee column it actually does the inverse of what you would think. Any value above 1 is evaluated as True in the conditional clause section which then evaluates -Sum(.005*[@Qty]) instead of 0 and the vice is true, any PPS less than 1 is evaluated to False.

Is there a quirk that i havent noticed before when dealing with row reference in tables of Excel that would cause this nature?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GoldBishop
  • 2,820
  • 4
  • 47
  • 82
  • See @Gimp's answer. To expand more, the `IF` formula works like this: `IF Condition is True, Then, Else` , so as written above, your Fee formula says `**IF** the value in PPS is greater than 1, **THEN** negate the product of the quantity value *.005, **ELSE** 0` – Scott Holtzman Sep 17 '12 at 23:45
  • 1
    damn your right, been looking at greater than and less thans too much today....gotta love the minor syntactical problems. – GoldBishop Sep 17 '12 at 23:47

1 Answers1

2

Have you tried this for your fee?

=IF([@PPS] > 1, 0, -SUM(0.005 * [@Qty]))

Your question's confused me a bit, so please comment if i'm missing something and i'll revise/ delete my answer.

danielpiestrak
  • 5,279
  • 3
  • 30
  • 29
  • The problem is that i am getting the inverse of the logic. If the `PPS` value is `Less than 1` it should result in the negative calculation. If the value is `greater than 1` then it should result in a 0. – GoldBishop Sep 17 '12 at 23:40