Multiple IFs and maximum value

[pao13]

Hi. I'm attaching my workbook. In column G starting from G6 I want a formula that gives me 100 if K5 is positive. If its negative and the absolute of K5 is smaller than I6, then I want 100 again. But if the absolute of K5 is larger than I6 then I want G6 to be K5 divided by I6 *100 (K5*100/I6) to the nearest integer. The cells in column G need to have a maximum value of 5,000/C6.

Thanks for any help!

 

[Hui]

I6 is based on G6 and you want G6 to be based on I6
That will create a circular reference error

Can you please check your logic

 

[pao13]

Thank you for your answer! Sorry if I'm not explaining it correctly. English is not my native language.

Let's explain what I'm trying to accomplish with the adjustment in options (column G). Column K is my profit or loss up to that point. So if I have a loss (eg. K7) and the potential profit in the next row (eg. I8) does not cover my loss I want to increase the number of options (and hence the potential profit) to cover the loss. So for instance G6 will stay 100 because I6>K5. But G8 will have to change because I8 is lower than ABS(K7). So G6 will have to become 100*25,246/6,897 =366.

I would like something like this

IF(K6="",MIN(IF(OR(K5>0,ABS(K5)<I6),100,K5*100/I6),5000/C6),"")

This function works but only when 100 stays. I'm attaching my updated workbook. Column N has the results of the above formula (changed because I want odd rows to have equal numbers with even ones).

When 100 in column G has to be eg. 366 (N8) all the results of the formula change again. While N10 is 292 now, if I change G8 to 366 then N10 will have to be 100. So the formula needs to check every time column K. Maybe this can be done with a VBA only?

Thanks for any help!

 

[pao13]

Hi. I'm following up with this problem because it's really bothering me. I'm attaching my workbook again. This formula is what I want

=IF(AND(MOD(ROW(),2)=0,B6<>""),ABS(IF(K6="",MIN(IF(OR(K5>0,ABS(K5)<I6),100,-K5*100/I6),$Y$5/100/E6),IF(AND(MOD(ROW(),2)=1,B6<>""),M5,""))))

This is in column M. Column N is rounding up column M. The problem is that if I copy column N to column G a circular reference error comes up. So what I would like if possible is a macro or formula in column G that takes one cell at a time of column N.

When I change manually G8 for 366 (N8), then column N changes again.

Hopefully you understand what I'm trying to do!
Thanks for any help!