mymeeshell
New Member
Hi all,
I am building a sales model where I'm trying to determine how many accounts we'll bring on board and when. I have three scenarios, high, medium, and low. To simplify things, I said that one sales person will close 1 accounts per month in the high scenario, .5 in the mid scenario, and .25 in the low scenario.
So let's say I'm in the mid scenario and I have whats in the sample file (.5 in each month). Now, it's not realistic to close HALF an account in a given month (you either get one or you don't). So I want to convert this info into "Actual Accounts Closed" as you'll see on the sample. What I'm trying to do is come up with ONE formula that will automate this calculation for "Actual Accounts Closed" that will work across all scenarios. In the example, I typed out how it should look for the Mid scenario manually. Obviously in the High scenario, the value will be 1 each month and for Low it will be 1 every 4 months.
I tried
=IF(INT(C2),C2,IF(INT(SUM(B2:C2)),SUM(B2:C2),0))
which worked great for cell C3 (month 2), but this completely breaks down after the 2nd month (in month 3 for example, it thinks I'm adding a whole new account when really I'm waiting til month 4 to close that account). It also breaks down completely in other scenarios.
I might be overcomplicating this, so if anyone has a simpler set up to achieve the same result (having new accounts come online with different frequency depending on the scenario), please let me know as I'm currently driving myself bananas.
I am building a sales model where I'm trying to determine how many accounts we'll bring on board and when. I have three scenarios, high, medium, and low. To simplify things, I said that one sales person will close 1 accounts per month in the high scenario, .5 in the mid scenario, and .25 in the low scenario.
So let's say I'm in the mid scenario and I have whats in the sample file (.5 in each month). Now, it's not realistic to close HALF an account in a given month (you either get one or you don't). So I want to convert this info into "Actual Accounts Closed" as you'll see on the sample. What I'm trying to do is come up with ONE formula that will automate this calculation for "Actual Accounts Closed" that will work across all scenarios. In the example, I typed out how it should look for the Mid scenario manually. Obviously in the High scenario, the value will be 1 each month and for Low it will be 1 every 4 months.
I tried
=IF(INT(C2),C2,IF(INT(SUM(B2:C2)),SUM(B2:C2),0))
which worked great for cell C3 (month 2), but this completely breaks down after the 2nd month (in month 3 for example, it thinks I'm adding a whole new account when really I'm waiting til month 4 to close that account). It also breaks down completely in other scenarios.
I might be overcomplicating this, so if anyone has a simpler set up to achieve the same result (having new accounts come online with different frequency depending on the scenario), please let me know as I'm currently driving myself bananas.