Combining Formulas

[Alex501]

Hi

We have originally added fees to accounts based on a percentage of the original balance. We have a new client who wants their fees capped to specific amounts. I've done the formula for the new cliwnt but I'm struggling to add the equation to the original equation. The data looks like this;

[TABLE="width: 547"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total[/TD]
[TD]Additional Fee[/TD]
[TD]Fee %[/TD]
[TD]Old Client[/TD]
[TD]Old Fee[/TD]
[TD]New Fee[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2000.00[/TD]
[TD]20.00[/TD]
[TD]20[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1500.00[/TD]
[TD]15.00[/TD]
[TD]20[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]500.00[/TD]
[TD]5.00[/TD]
[TD]20[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]150.00[/TD]
[TD]15.00[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]60.00[/TD]
[TD]10.00[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The equation I have for the old clients is (E2);

ROUND((((A2+B2)/100)*C2),2)


And the formula for the new clent (F2);


IF(A2<100,10,IF(A2<250,25,IF(A2<500,50,IF(A2<1000,75,IF(A2<5000,75,IF(A2<10000,100))))))


How do I combine the equation bases on whether or not column 'D' has 'Yes' in it? I've got as far as;


ROUND(IF(D2="Yes",((A2+B2)/100)*C2)),2)


Any help is much appreciated.


Thanks


Alex

 

[JazzSP8]

Try

=IF(D2="Yes",ROUND((((A2+B2)/100)*C2),2),IF(A2<100,10,IF(A2<250,25,IF(A2<500,50,IF(A2<1000,75,IF(A2<5000,75,IF(A2<10000,100)))))))

Does that work for you?

 

[Alex501]

Hi JazzSP8

Perfect, thank you so much.

Alex