Conditional Sum function does recognise %

G

Geoff Newham

The Conditinal Sum Function is not recognising a column that calculates a
percentage of 3 other columns (e.g. (S-T)/R is a calculation that ends up in
V).
When filtering for conditions where one includes the total consideration of
all those at 0% and, then, at more than 0%, the calculation always returns 0.
Applying the other conditions independently of the % column is fine. As soon
as the % column is included, the result returns to 0.
On some occasions, in Step 2 of the Conditional Sum Wizard, selecting a
condition based on the % column returns #### in the value box before the
condition is added
 
G

Geoff Newham

Niek,
Thanks for the reply.
However, I do not have any formulae to post.
I cannot complete the formula for this specific task because of the problems
mentioned in my first post.
starting with a Trade date, then:
Column 1 = Price
Column 2 = Quantity
Column 3 = Price x quantity
Column 4 = Total Consideration (amount actually paid)
Column 5 = Difference between 3 and 4
Column 6 = Stamp duty payable (if and when this applies[from elsewhere])
Column 7 = Accrued Interest (if and when this applies[from elsewhere])
Column 7 = Commission payable as %. (=(5-6&7)/4)
Column 8 = Broker name
So a conditional sum calc involves: identifying for all trades within a time
period, each broker by name and calculating where Comms payable = 0%, where
comms payable is more than 0% and where that's the case the total amount of
Total Consideration where these criteria are met.

So I end up with a total consideration paid to each broker, within a set
time period where the commission payable to each broker is 0% and another
calc where the commission payable is more than 0% although, for this 2nd one
I can substract the total under 0% from the total of all considerations paid.
I cannot find how to calculate the amount of 0% against these columns.

Column to sum = Total Consideration
Condition1 = Broker name = Credit Suisse (for example)
Condition 2 = time period > 30/4/09
Condition 3 = commission payable = 0%
It's when I come to the last condition that I end up with #### in the drop
down menu of possible entries.
If I over-ride the #### and enter "=0%" or ">0%" or any other combination, I
end up with a calculated answer of 0.00, which is incorrect.

I don't know if this is sufficient to help you on this one, Niek, to
understand what's being attempted, but I cannot think how else to describe it.

Regards

Geoff.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top