Blinds Setup 99% done (1 Viewer)

[paiNyy]

I just want to round things (left) up or down, but in an specific way i guess, but i dont know how.. Any solutions/ideas?
Thanks in advance.

 

[BGinGA]

L1 50 100
L2 75 150
L3 125 250
remove T25 chips
L4 200 400
L5 300 600
L6 500 1000
L7 800 1600
L9 1200 2400
remove T100/T500 chips
L10 2000 4000
L11 3000 6000
L12 5000 10000

 

[paiNyy]

L1 50 100
L2 75 150
L3 125 250
remove T25 chips
L4 200 400
L5 300 600
L6 500 1000
L7 800 1600
L9 1200 2400
remove T100/T500 chips
L10 2000 4000
L11 3000 6000
L12 5000 10000

Thats just an sample output. I need the solution for excel itself, but maybe I ve to go into an excel forum for this. :-D
1) Round up or down to 25s
2) after Level X round up or down 50s
3) after Level X round up or down 100s
4) after Level X round up or down 1000s

 

[paiNyy]

Ah my headline got changed maybe because an specific word.

Blinds Setup 99% done - Need an Excel pro​

 

[dpeks13]

The rounding is inconsistent so that makes it difficult, but you can try something like MROUND to get it to the nearest multiple. You'll just have a few different formulas

 

[springbox]

You could do this with nested IFs and MROUND, but it's probably easier to just skip the IFs and define a few formulas.

Something like:

=IF(LEVEL>X,IF(LEVEL>Y,IF(LEVEL>Z,MROUND(VAL,1000),MROUND(VAL,100)),MROUND(VAL,50)),MROUND(VAL,25))

The chain will go like
IF LEVEL <= X round value to the nearest 25
IF LEVEL > X check if LEVEL > Y
IF LEVEL <= Y round value to the nearest 50
IF LEVEL > Y check if LEVEL > Z
IF LEVEL <= Z round value to the nearest 100
IF LEVEL > Z round value to the nearest 1000

 

[Eriks]

 

[springbox]

Adding another reply, because for some reason I like looking up excel documentation...

If you have Office 2019 or newer, they added an IFS function that's simpler than nesting ifs. It evaluates the first true condition, and returns the corresponding value. The order matters, so if we're evaluating by greater than we should start with the largest level, and if we're evaluating by less than we should start by the smallest value.

=IFS(Condition1,Return1,Condition2,Return2,Condition3,Return3,...)

In our case:

=IFS(LEVEL>Z,MROUND(VAL,1000),LEVEL>Y,MROUND(VAL,100),LEVEL>X,MROUND(VAL,50),TRUE,MROUND(VAL,25))

If LEVEL <= Z & LEVEL <= Y & LEVEL <= X we'll hit the default condition (TRUE) and round to 25.

 

[dpeks13]

So what we're saying is: Probably makes sense just to do it manually.

 

[Mr Winberg]

So what we're saying is: Probably makes sense just to do it manually.

What's the fun in that?

 

[dpeks13]

 

[LeLe]

There's template and great tested blind structure out there to follow and use.

I don't see the extra step needed of getting the formula of the blind, it is not like your group will question how the F do you get this blind structure, what is your formula and basic for calculation.

 

[BGinGA]

You can't paint a masterpiece with Excel, either. It's just a tool, with limitations.

 

[paiNyy]

Adding another reply, because for some reason I like looking up excel documentation...

If you have Office 2019 or newer, they added an IFS function that's simpler than nesting ifs. It evaluates the first true condition, and returns the corresponding value. The order matters, so if we're evaluating by greater than we should start with the largest level, and if we're evaluating by less than we should start by the smallest value.

=IFS(Condition1,Return1,Condition2,Return2,Condition3,Return3,...)

In our case:

=IFS(LEVEL>Z,MROUND(VAL,1000),LEVEL>Y,MROUND(VAL,100),LEVEL>X,MROUND(VAL,50),TRUE,MROUND(VAL,25))

If LEVEL <= Z & LEVEL <= Y & LEVEL <= X we'll hit the default condition (TRUE) and round to 25.

I got O365 - Thanks a lot, I will try it out!!

You can't paint a masterpiece with Excel, either. It's just a tool, with limitations.

check this out, thats what I call an masterpiece ;-) -

There's template and great tested blind structure out there to follow and use.

I don't see the extra step needed of getting the formula of the blind, it is not like your group will question how the F do you get this blind structure, what is your formula and basic for calculation.

Nahh, that's just for me - I like to do stuff myself.

It works - Pretty helpfull big thanks!! @springbox

=WENNS(C19>2000;VRUNDEN(C19;1000);C19>500;VRUNDEN(C19;100);C19>170;VRUNDEN(C19;50);WAHR;VRUNDEN(C19;25))

 

[buzzmonkey]

It's just a tool, with limitations.

Like @Anthony Martino.

 

[BGinGA]

I got O365 - Thanks a lot, I will try it out!!

It works

View attachment 1166230

Except that it really doesn't work, in terms of creating a balanced blind structure.

Add another column displaying the percentage increase per level. It varies way too much.

 

[paiNyy]

Except that it really doesn't work, in terms of creating a balanced blind structure.

Add another column displaying the percentage increase per level. It varies way too much.

Yeah, but i'm one step further. Now I have to reset the Levels, where we remove the T25/T100/T500 chips (maybe works then only for my T5K start, but we dont use others anyway).

Good hint to get the percentages increase displayed.

 

[paiNyy]

@springbox
the best solution is to put more conditions.

Im pretty happy with the results, check bellow.

=WENNS(
C23>5001;VRUNDEN(C23;1000);
C23>2001;VRUNDEN(C23;500);
C23>1001;VRUNDEN(C23;200);
C23>501;VRUNDEN(C23;100);
C23>238;VRUNDEN(C23;50);
WAHR;VRUNDEN(C23;25))