Monte Carlo constraints

**Hereward** · 05-23-2013, 12:34 PM

Hello

I am trying to run a monte carlo simulation on @risk however I need to set up some constraints and I am having trouble developing the correct formula.

if the sum of a row of cells (each cell is one month) equals a value in a separate cell (call it x) I want the formula to return 0, however if the cell is lower than that value (y) I would like it to either return a desginated minimum value or x-y; whichever is lowest.

I have been trying to write an if argument however I cant complete the end of it so far it looks like this:

if(sum(g70:g70)=c$64,0 (if(sum(g70:g70)<c$64....now i want to say either return the designated minimum value or difference between c$64 and the sum of the previous months; which ever value is the lowest.

I hope my question is clear enough to understand.

Thank you for any help.

**MrShorty** · 05-23-2013, 01:07 PM

if the sum of a row of cells (each cell is one month) equals a value in a separate cell (call it x) I want the formula to return 0, however if the cell is lower than that value (y) I would like it to either return a desginated minimum value or x-y; whichever is lowest.

It would seem to me that you don't need a separate condition/test for does x=y then =0, because, if x=y, then x-y=0 (within round off error, anyway).

One clarification: we are sometimes careless -- especially when talking about negative numbers -- in defining what "minimum" means. Do you mean the value closest to 0 or the value further away from 0? Mathematically, minimum means "more negative" -- meaning that MIN(-10,-5) is -10.

As I interpret your statement, you want the function to return a value between 0 and a designated minimum. The simplest statement might be something like =MAX(x-y,designated minimum). Assuming x is always less than y, this function will return 0 when x=y, x-y when x-y is between 0 and minimum, and minimum when x-y is less (more negative) than minimum.

**Hereward** · 05-23-2013, 01:24 PM

Mr Shorty,

I think perhaps my explanation was rather unclear and also used incorrect terms.

I have created a hypothetical attached.
What I am trying to do is in the Yellow highlighted rows enter a formula that we take, september for example, if b5:b5 equals r6 return the value 0. If the value of b5:b5 is lower than r6 then either return g11 or return the the difference between b5:b5 and r6. Whichever value is lower (and yes closer to 0)

The idea is to prevent the simulation from using the minimum threshold when the amount of stock (here apples) is below that months minimum threshold.Apples.xlsx

**MrShorty** · 05-23-2013, 02:10 PM

It still looks to me like some variation of the MAX() function I proposed should work =MAX(sum($B5:F5)-$R$6,G11). Have you tried it? What results did you get? If they were incorrect, why and when were they incorrect?

**Hereward** · 05-24-2013, 02:13 AM

Thank you again MrShorty,

I can see how the formula you suggested would work however when I input it into one of the cells the value of 0 is returned. Considering there are no other values in the row the threshold number should be displayed because the sum of the entire empty row is not above R6.

Thank you again for your help.

**MrShorty** · 05-24-2013, 08:27 AM

when I input it into one of the cells the value of 0 is returned.

I think I'm missing something here. the sum of blank cells should be 0. 0-28,000 is -28000. the larger value between -28000 and g11 (2300) is 2300. the larger value between -28000 and -g11 (-2300) is still -2300. I don't see where the 0 is coming from.