Need help with an even more complex nested IF statement

[#1]

Thought I could pull this off with what Ram just helped me with, but this one had a greater number of outcomes and not sure which to approach first.

The result column I am populating is called Excess_Allocation.

The columns I am using for this are:

Flag

Excess

Allowance

Fees

The logic goes like this:

IF Flag = ' '

     IF Fees > Allowance

          AND SUM(Excess) > 0

          AND SUM(Excess) > Fees - Allowance

     THEN Excess_Allocation = Fees - Allowance

     IF Fees > Allowance

          AND SUM(Excess) > 0

          AND SUM(Excess) < Fees - Allowance

     THEN Excess_Allocation = SUM(Excess) * (Fees - Allowance) / (SUM(Fees) - SUM(Allowance))

     IF Fees < Allowance

     THEN Excess_Allocation = 0

IF Flag = 'x'

     THEN Excess_Allocation = 0

Thanks for your help!

[#2]

David,

Where you are trying to do at Script level or at the Expression, Can you also give me the sample data file. So i can work and get back to you.

-Ram

[#3]

Its an Expression

The columns all hold numbers really, except Flag, which uses either an x or a ''.

[#4 · Accepted solution]

David,

Can you try this

if(Flag='',

  if(Fees > Allowance and sum(Excess) > 0 and sum(Excess) > (Fees-Allowance),Fees-Allowance,

  if(Fees > Allowance and sum(Excess)>0 and sum(Excess) < (Fees-Allowance), ((sum(Excess)*Fees-Allowance)/ (sum(Fees)-sum(Allowance))),

  if (Fees < Allowance, 0)),0),0)

Not sure whether this will work, but with tweaks with the braces you can make it work.

-Ram

[#5]

Ok I will give it a go many thanks