Increment A1 by 1, until B1(=index function of A1*X) becomes >1

Hello,

Please help me doing this:


B1=X * an INDEX function of A1
I want to start increment A1 cell by adding 1 until B1 becomes >1

How can I do that ?



Thanks in advance

Please show the exact formula in B1. Your title and your description don't match and it is unclear what that formula looks like.

What is in A1? Just a numeric value?

This information will help determine whether this can be done with formulas, or whether you require a macro.

Sorry for that I thought I simplified my question.

The number in A1 is the row number where the steel section data is found.

B1 is the safety factor result of my steel design procedure, B1 comes from other cells calculations but most of those cells are an INDEX formula of A1, they take the steel section data from the row number I am now putting manually in A1 and keep increasing by 1 untill the safety factor B1 becomes larger than 1.


If it still not clear I will upload my sheet



Thanks

Details would help. My first thought would be to approach this differently. Rather than some brute force trial and error algorithm trying to find the value in A1 that makes B1>1, I would turn the function in B1 around (find its inverse in math parlance). The problem then turns into a lookup type function that will take a value of 1 (from B1) and directly tell me what value should be in A1. Since you have not shared the exact function in B1, I cannot help with finding its inverse, but that may not matter if you are able to do so.

Which is not to say that your brute force increment A1 until B1>1 algorithm cannot work, but there are several algorithms I would try before resorting to this brute force algorithm.

You will excuse me if I don't try to reverse engineer that much spreadsheet. If a simple inverse function is possible, it will be up to you to know the math behind the problem and perform the necessary inversion.

Since I did not want to do that, I tried another alternative -- 2D data table: http://www.excel-easy.com/examples/data-tables.html
1) I entered 6 into C.K.!D5, then edited SHS!O1 to be =C.K.!$D$5 (so that my input value would be on the same sheet as the output value).
2) In some out of the way place (I chose E47 and down), enter 6, 16, 26, ... (I was too lazy to enter every integer, so I went by 10's, you can enter by 1's, if you want).
3) Enter =B31 into the cell above and to the right of the first entry (F46 in my case).
4) Select E46:Fxx and execute the Data -> What if -> Data table -> column input reference -> D5
5) Scan the resulting data table to see where the value crosses 1 (If I understood correctly, it looks like the result is between 66 and 76).

Is that something you can work with?

I cannot, in part because I am too lazy to completely reverse engineer your spreadsheet/calculations. I am not sure which calculations are correct and which aren't correct.

I did make this interesting observation. Upon changing B4, I notice that the spreadsheet seems to go through 2 calculation events, as the value in B29 changes briefly (which changes the value in the colored A32), then settles on a second value. The data table seems to capture the value from the first calculation event and not the second. I found this easier to see by adding a large lookup table and several slow linear searches on that lookup table to slow down the first calculation to see which values in column B changed on input and which did not. (for example, when I do this, I can see that upon the initial change to B4, B29 changes immediately to the value given in the data table, but then B28 and B29 change again at the end of the calculate event.)

If I change the value in B4, then press F9 (to trigger another calculate event), then the table updates so that the value for the current value in B4 matches the result in the table.

I do not see a quick and easy explanation for this behavior. Not knowing which result is "correct", I cannot suggest a fix, either. My hypothesis at this point is that something somewhere across the workbook is causing Excel's calculation engine to calculate cells in the wrong order or something like that.

Originally Posted by MrShorty

I cannot, in part because I am too lazy to completely reverse engineer your spreadsheet/calculations. I am not sure which calculations are correct and which aren't correct.

I did make this interesting observation. Upon changing B4, I notice that the spreadsheet seems to go through 2 calculation events, as the value in B29 changes briefly (which changes the value in the colored A32), then settles on a second value. The data table seems to capture the value from the first calculation event and not the second. I found this easier to see by adding a large lookup table and several slow linear searches on that lookup table to slow down the first calculation to see which values in column B changed on input and which did not. (for example, when I do this, I can see that upon the initial change to B4, B29 changes immediately to the value given in the data table, but then B28 and B29 change again at the end of the calculate event.)

If I change the value in B4, then press F9 (to trigger another calculate event), then the table updates so that the value for the current value in B4 matches the result in the table.

I do not see a quick and easy explanation for this behavior. Not knowing which result is "correct", I cannot suggest a fix, either. My hypothesis at this point is that something somewhere across the workbook is causing Excel's calculation engine to calculate cells in the wrong order or something like that.

Yes you are right, there is a second calculation event this new data table function can not catch.
It comes from the problem you brilliantly solved for me earlier, everytime I change B4 cell (column height), the data table function that you suggested me to use few days ago will check all column sections in SHS sheet and another function will choose the smallest satisfactory one, this will change all numbers in C.K sheet for the new chosen section in sheet SHS cell O1.

Anyway it is not a big deal thank you very much for your time.