# Thread: Array formula which includes (1-n%) (XL97 Sr2h)

1. ## Array formula which includes (1-n%) (XL97 Sr2h)

My array formula skills are weak, and I can't for the life of me figure out how to do a one cell formula in cell F10 in the attachment. Can't get an =sumproduct() version to work either.

By way of explanation (?and simplification), we offer a range of service products with a monthly fee, and forecast annual margin dollars on what the salesperson projects the customer will buy. In the example columns A3:E8 are hard coded, in real use they come from another series of pricing sheets. The final sale may reflect both best-&-final rates, a different product mix from projected, and also a different participant mix, so we report back the annualized margin change from original projected to final sold. (It's more complicated when we are renewing a customer, but I don't need to go there. There's also a mere 50 or so products.)

The dollar margin for the bafo per product is: {bafo price} minus {original price} * (1 minus {original margin}) * {participant count} * 12. In effect product cost is constant, just the final price varies, and the corresponding margin.

I'm trying to get a one-cell array formula that gives me the final margin dollars, without having to use the intermediate calcs in column G. There's some other "proofs" in the attachment so you can see what I'm doing (I hope they are not confusing). You'll also see that I was trying to have the BAFO price be it's own Boolean. The "(1 minus {original margin})" is what's confusing me.

Anyone able to figure out what formula works?

2. ## Re: Array formula which includes (1-n%) (XL97 Sr2h)

Try this array formula:

<pre>=SUM((F3:F8<>"")*(F3:F8-(C3:C8*(1-D38)))*12*B3:B8)
</pre>

3. ## Re: Array formula which includes (1-n%) (XL97 Sr2h)

Thank you, thank you, thank you! <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

FWIW I'll probably use {=SUM((F3:F8<>0)*(F3:F8-(C3:C8*(1-D38)))*12*B3:B8)}

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•