Hi, I've been using this forum for a quite a while, but I never posted because most of the time, I found what I was searching for through the search engine. Though, this time it seems I'm finally working on something nobody ever asked about. So, here is my problem...
I'm working on a spreadsheet to anticipate the corrosion rates on multiple equipments, based on 4 criterias. At the moment, we type them down one by one and we search for the corresponding value inside the table we have.
<tbody>
</tbody>
These values aren't the real ones, but it looks like that. So, if I have a piece of equipment made of CS, I look at the specific table for CS (There are about 7 different groups of materials). I consider the %Sulfur, TAN# and the specific temperature. You also have to consider that this table is repeated with different outputs for higher level of %sulfur (>0.6, up to about 3%)
EX - Material = CS, %Sulfur = 0.4, TAN# = 0.7, Temp = 152, my value will be 9 (the bold numbers are the outputs). There is no linear relation or any sort of mathematical way to obtain the output, you have to look at the table.
In my mind, this part of my spreadsheet should look just like that :
<tbody>
</tbody>
The excel part now, I tried working with IF() to specify a table to use as the TRUE value, trying to group multiple IF statement into one, but searching values inside multiple ranges is very tedious and I cannot cover all the possibilities... Then I tried working with INDEX() and MATCH() but again, these are not exact values, they are ranges, making the work even harder. Building an output with multiple criterias that are simple and exact is easy, I just can't seem to work it out with ranges.
I must admit that I don't know a lot about VBA, but if it's the only way out, I'll learn how to do it. Though, I'm almost sure that it could be done using simple excel formulas. The worst part is that I'm almost sure that I could do it using MATLAB, but the company I'm working for isn't using it - and - won't buy it haha. Finally, can it be done ? I'm stuck and could use some help, even if it's only where I should be working on, what I should search for.
Thanks !
(I'm sorry if it's hard to read sometimes, english isn't my first language)
I'm working on a spreadsheet to anticipate the corrosion rates on multiple equipments, based on 4 criterias. At the moment, we type them down one by one and we search for the corresponding value inside the table we have.
Temperature | ||||||
CS (carbon steel) | %Sulfur | TAN | [< 50-100] | [>100 - 150] | [>150 - 200] | [>200 - 250] |
<0.3 - 0.6 | < 0,3 - 0,6 | 1 | 2 | 4 | 5 | |
> 0,6 - 0,9 | 8 | 13 | 14 | 18 | ||
> 0,9 - 1,2 | 32 | 43 | 54 | 61 | ||
> 1,2 - 1,5 | 100 | 120 | 135 | 148 |
<tbody>
</tbody>
These values aren't the real ones, but it looks like that. So, if I have a piece of equipment made of CS, I look at the specific table for CS (There are about 7 different groups of materials). I consider the %Sulfur, TAN# and the specific temperature. You also have to consider that this table is repeated with different outputs for higher level of %sulfur (>0.6, up to about 3%)
EX - Material = CS, %Sulfur = 0.4, TAN# = 0.7, Temp = 152, my value will be 9 (the bold numbers are the outputs). There is no linear relation or any sort of mathematical way to obtain the output, you have to look at the table.
In my mind, this part of my spreadsheet should look just like that :
Material | %Sulfur | TAN# | Temperature | CORR RATE |
W | X | Y | Z | = Output |
<tbody>
</tbody>
The excel part now, I tried working with IF() to specify a table to use as the TRUE value, trying to group multiple IF statement into one, but searching values inside multiple ranges is very tedious and I cannot cover all the possibilities... Then I tried working with INDEX() and MATCH() but again, these are not exact values, they are ranges, making the work even harder. Building an output with multiple criterias that are simple and exact is easy, I just can't seem to work it out with ranges.
I must admit that I don't know a lot about VBA, but if it's the only way out, I'll learn how to do it. Though, I'm almost sure that it could be done using simple excel formulas. The worst part is that I'm almost sure that I could do it using MATLAB, but the company I'm working for isn't using it - and - won't buy it haha. Finally, can it be done ? I'm stuck and could use some help, even if it's only where I should be working on, what I should search for.
Thanks !
(I'm sorry if it's hard to read sometimes, english isn't my first language)