Forum Discussion
Help with Conditional Function
I need assistance creating a conditional function for a unique calculation.
The Scenario is as follows:
For a given month I have a "Hurdle" that must be overcome for progress. EG the hurdle is 100,000. I sell 125,000 so I receive 25,000 as credit. So by doing 125,000 - 100,000 = 25,000
Next month I do the same with the same hurdle of 100,000. I sell 125,000 so I receive another 25,000 as credit. Again, this months cell is 125,000 - 100,000 = 25,000
My total gain is now 50,000
On month 3 My hurdle is 100,000 but I only sell 40,000 for a total negative to goal of 60,000.
In my scenario, I cannot lose more than I've gained in months prior so the cell cannot simply be calculated by doing 40,000 - 100,000 = -60,000. I need this months formula to yield the result of -50,000 as that is the total amount I gained in months prior.
In summation, I need a formula that if the result of the sales minus the hurdle is a net positive, simply output the net positive. If the sales minus the hurdle is a net negative, output the net negative only up to the point of the sum of the prior net gain and not more.
Thank you so much in advance for the help. I don't know how to apply the logic to a formula as I am not that adept with the function language.
See if the attached workbook does what you want.
- This is exactly what I was looking for!
Is there anyway to ensure that Column E (Cumulative Credit) cannot go negative. That may be the only pit fall to the set up. If at any point that column were to go negative it could throw off Column D "Credit"The formula in column D ensures that the cumulative credit in column E never becomes negative
