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
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
Last edited by M_jordan; 08-16-2017 at 08:43 AM.
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
Last edited by M_jordan; 08-16-2017 at 09:01 AM.
The goal is just finding the smallest steel section from the table that will make the safety factor in B1 > 1 (i.e. the smallest number in A1 to make B1 >1)
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.
Originally Posted by shg
oh sorry forgive me guys, can you please refer to my attached excel worksheet ?
here is exactly what I seek:
I need that:
cell: C.K.!B31 (=C.K.!B29/C.K.!B28) to be just above 1
by changing the number in cell SHS!O1 from 6 to 152, which is the row number range of the table you can see in the same sheet SHS.
for example when I change the column height (C.K.!B4) I want my Excel to automatically do the operation again to find the smallest section from the table in SHS to satisfy that C.K.!B31>1.
I apologize guys for the inconvenience
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?
Yes I definitely can work with this.
BTW I should have said the safety factor should be just below 1 and above 0, not above 1, how dumb I am.
Thank you very much MrShorty you helped me a lot.
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.
Last edited by M_jordan; 08-24-2017 at 03:10 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks