Sum [Price] IIF [BelowSetFloor] 0

How do I write the calculation?

for this consignor group I have to list each element of the consignor
settlement as a separate item - provide a step by step calculation for each
variable (they want to audit each calc)

So I first have gross sales= Sum([Price])+Sum([Adjust Sale Price])

next calc I need to do is to take result of this field and subtract prices
for lots that no saled because the purchase price was below the established
minimum floor. These lots can be identified by a currency field
[BelowSetFloor] which is either = 0 (lots sold = minimum purchase price) or
= 100 (lots sold minimum purchase price)

Want to create a calculated field that will total only the sale prices where
the record also has BelowSetFloor = 100

So if consignor had 5 lots

1 - Price = 1500, BellowSetFloor = 0
2 - Price = 1100, BellowSetFloor = 0
3 - Price = 1100, BellowSetFloor = 100
4 - Price = 1100, BellowSetFloor = 100
5 - Price = 1500, BellowSetFloor = 0

the new calculated field would = 200

This calculated field will then be subtracted from the gross sale field
identified above

figured it out -

=Sum(IIf([BelowSetFloor]0,[Price],0))

"rcs" wrote:


How do I write the calculation?

for this consignor group I have to list each element of the consignor
settlement as a separate item - provide a step by step calculation for each
variable (they want to audit each calc)

So I first have gross sales= Sum([Price])+Sum([Adjust Sale Price])

next calc I need to do is to take result of this field and subtract prices
for lots that no saled because the purchase price was below the established
minimum floor. These lots can be identified by a currency field
[BelowSetFloor] which is either = 0 (lots sold = minimum purchase price) or
= 100 (lots sold minimum purchase price)

Want to create a calculated field that will total only the sale prices where
the record also has BelowSetFloor = 100

So if consignor had 5 lots

1 - Price = 1500, BellowSetFloor = 0
2 - Price = 1100, BellowSetFloor = 0
3 - Price = 1100, BellowSetFloor = 100
4 - Price = 1100, BellowSetFloor = 100
5 - Price = 1500, BellowSetFloor = 0

the new calculated field would = 200

This calculated field will then be subtracted from the gross sale field
identified above