Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - recognise #N/A | Excel Discussion (Misc queries) | |||
Can i get a sum function to recognise X (letter) as a number? | Excel Discussion (Misc queries) | |||
How to get an Excel function to recognise text | Excel Discussion (Misc queries) | |||
let a sub recognise the name of the button that has been clicked. | Excel Discussion (Misc queries) | |||
Is there a way for excel to recognise that €0.00 = 0 for the purposes IF function ? | Excel Discussion (Misc queries) |