Conditional Sum function does recognise %
 

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

--
Geoff

Conditional Sum function does recognise %
 

Always post your formulas.
What are the values involved?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Geoff Newham" wrote in message
...
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

--
Geoff

Conditional Sum function does not recognise %
 

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.

--
Geoff


"Niek Otten" wrote:

Always post your formulas.
What are the values involved?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Geoff Newham" wrote in message
...
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

--
Geoff