search

pyscripter / XLRisk

Excell addin for performing Monte Carlo simulation
MIT License
112 stars 24 forks source link

Function OneRiskFunctionPerCell #18

Closed SaraP00 closed 2 months ago

SaraP00 commented 2 months ago

Hello everybody, I have tried to implement XLRisk for a project and it's fastastic. The only downside is that I'm not allowed to use the IFS function that basically use a specific prob. function depending on the the type of distribution I choose. I know that its not a big of a deal writing directly the risk function in the cell but i need to make it as easier and "automated" as possible. I tried to add a new function in the macro library with the help of chat gpt since i'm not used to write in VBA...this strategy was not a success i must say. Is there a way to avoid this issue?

pyscripter commented 2 months ago

XLRisk only allows one risk function per cell. However, you can achieve the desired result, similar to IF and IFS, by using multiple cells, e.g.

A1: =RiskNormal(100, 20) A2: =RiskTriang(80,100,120) A3: =If(condition, A1, A2)

SaraP00 commented 2 months ago

thank you for the suggestion, I implemented it but the last limitation is that the function gives an errror when calculating, in this case the function RiskTriang but the inputs are just to use RiskUniform.

The error comes from the function "Public Function SortedTable", specifically in the line ColVals(I) = Abs(Table.Cells(I, Col).Value) because, as I imagine, it has to collect from the selected cell a value that is a positive number, not a text or a negative number.

Is there a way to modify this function? Thank you again for your support.

pyscripter commented 2 months ago

Can you post your excel model here, so that I have a look?

pyscripter commented 2 months ago

There are no XLRisk functions in this worksheet. And cell S1 has an error value.

If you were trying to enter the RiskTriang formula in your screenshot, the problem is that the most likely value is greater than the min. This is why you are getting an error.

SaraP00 commented 2 months ago

I'm sorry the functions got removed when I created the copy. I managed to solve the problem. Thank you again for your time and help!