EXCEL: Nested IF formula question [Solved/Closed]

Report
Posts
2
Registration date
Thursday April 23, 2009
Status
Member
Last seen
April 29, 2009
-
 Tonio -
Hello,

I've read some answers to similar questions, but not quite the same...

How can I have *certain* results of my nested "IF" formula display in a different cell than the cell that the nested IF formula resides in?

Here's my formula which currently works (residing in cell (E8), I just want it to be different:

=IF(AND(B8>500000,B12>40000),(C8*B8)+(C12*B12),IF(AND(B8>500000,B12<40000),3000,IF(B8<500000,
"None")))

This is a "Bonus" formula, where if sales of "X" (B8) are more than "$SalesTarget1" AND ALSO sales of "Y" (B12) are more than "$SalesTarget2", then SalesPerson gets a bonus of 1.5% (C8) of "X"TotalSales (B8) and 1% (C12) of "Y"TotalSales (B12).

I would like it if the SalesPerson meets the bonus criteria, that the result of 1.5% x "X"TotalSales would display in cell E10 (next to cell "Food Sales") and 1% x "Y"TotalSales would display in cell E11 (next to cell "Beverage Sales"

I tried changing the formula to ...........,(C8*B8=E10,C12*B12=E11),........... and some variations of this, but to no avail. If anyone knows how to do this, I will have great respect for your amazing EXCEL Prowess!

Thank you in advance for any help.

1 reply

Posts
2
Registration date
Thursday April 23, 2009
Status
Member
Last seen
April 29, 2009
3
I figured it out.

You have to use the Scenario Manager to display data in the manner I want to.

Data Tab>What If Analysis>Scenario Manager

In case anyone was needing this info.
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

bunda3, I see you used the Scenario Manager to come up with your solution, but after reading all the help files, I can't see how you specifically applied it to get your result. Can you elaborate? Thx