Need a good EXCEL person for a moment please.

[mrchaos101]

I am so close to haveing the fram work done I have a new issue that I cannot solve.

I need a good excel person to tell me how to adjust these:

Expected Attempts Formula :
=CEILING(1/(1-failure rate) * (desired yield / yield), 1)

Fail Rate:
= 1- success rate

Success Rate:
=((( Skill level - (trivial * .75)) +51.5)) /100



User Dynamic info are Desired Yield Skill Level and Trivial

Using 252 for the skill Level, as long as the Trivial is a high number it all works out. BUT if the item has a low trivial number, the the fail rate goes from a positive % to a -.

Meanng if I enter my 252 skill and the 335 trival for Kaladim Con's, the I get all correct data. BUT if I plug in my 252 skill and 68 trival for Ale then because the Ale at my skill level is "NO FAIL so to speak" it has a -% and the data is ruined.

I need a way to tell the Success rate skill to do the formula I gave it and USE that number as long as it is = or < 1.0


How do I do this?

[Turlo Lomon]

max ( .05, formula)

min (formula, .95)

My formula for this is almost a page long, taking into account variances at different skill levels, (below 15, below 68, below 200 skill, etc.)

Hope this helps.

[mrchaos101]

Cant sem to get it to work

The cell I want has this in it

=1 -B26

I dont ever want the cell to go above .99 this is my fail rate.

the other cell

has this in it

=(((E3-(F3 * 0.75))+51.5)) /100

I dont ever want the cell to get below 0 NO NEGITAVES

how do I do use your formula as mine have an = in them?

[Jarak_akn]

=MIN(1 -B26,.99)

=MAX((((E3-(F3 * 0.75))+51.5)) /100,0)


HTH

Jarak Note there are lots of other ways that give slightly different results

[Hibashira]

Just put a cap on your success rate. If you use 95%,

Success Rate:
=MIN(.95,((( Skill level - (trivial * .75)) +51.5)) /100)

[mrchaos101]

Lets try this

SKILL LEVEL = 252
Trival =335


CURRENT FORMULA FOR Success Rate %

=MIN ((((skill level - ( trivial * .75 )) + 51.5 )) /100 , 1)


This works great. AS LONG as the skill level is high (you can even if you put skill level 500) it keeps the VALUE in that cell to 1 ( to represent 100% success rate).

Now, if i keep the trival HIGH at 335 and start droping the skill level, the vallue in my success rate filed drops as I want it to. the problem is, if the skill level gets TOO low, then I start seeing the negitive numbers. I need it to allow for the skill level to drop also and once the value hits .01 it freezes and will not go into negitive numbers.

I have it set to MIN is there a way to add the MAX to the same line? so or is there like a RANGE funcion?

There, that post looks so much better. Thanks again for your time in helping me.

[Yakk]

=MIN ((((skill level - ( trivial * .75 )) + 51.5 )) /100 , 1)

Change to:
=MAX(0.03,MIN ((((skill level - ( trivial * .75 )) + 51.5 )) /100 , 1))

[mrchaos101]

Thanks

Thanks for the help. I think I have the fram work done.

I have started a new thread with a v1 beta attachment for any of you following this and would like to follow further.

Thanks for all your help!