I am struggling with a formula to get allowable stress for thickness calculation. Please look at the attached sheet and advise or write for me.
Require Allowable stress at "Column C" ( Tab Cal) from "Stress B31.3" Tab $C$5:$AE$8
Allowable stress depends on Material "Column B" and Temperature "Row 4".
Not all temperature for allowable stresses are available and has to interpolate for require temperature.
Re: A formula to get allowable stress FORECAST, OFFSET, INDEX, MATCH
Try this..
Formula:
=INDEX(StressTableB313,MATCH($A7,'Stress B31.3'!MaterialB313,0),LOOKUP($B7,'Stress B31.3'!$C$4:$AE$4,COLUMN('Stress B31.3'!$C$4:$AE$4)-3))
Check the attached file...
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Vikas_Gautam
Thanks for the formula. But this formula can't get correct value.
Last edited by AyeNu; 09-25-2014 at 03:59 PM.
Double-lookup using INDEX(MATCH,MATCH) for the stress values; approximate MATCH for the temperature; and linear interpolation.
I threw #N/A values in the blanks to force it to return errors outside the limit while I was building this; it also throws errors on the margins, that is, when you're right on the last data value. I dunno how you want to handle error catches, so you might want to just delete those #N/A and leave 'em blank.
Last edited by ben_hensel; 09-25-2014 at 11:45 AM.
To ben_hensel
Thanks for the formula. This formula can get correct value. But I do not know how you consider for material "column A" in "Cal" tab.
Could you please explain a bit?
Last edited by AyeNu; 09-25-2014 at 04:00 PM.
To ben_hensel
I just found hidden column C and D for Material and Temperature. Is there a way to write a formula without changing to index?
Last edited by AyeNu; 09-25-2014 at 04:01 PM.
@AyeNu
who are you responding to?
After replacing the blanks in the stress table with a big negative constant (-9E307),
C7: =PERCENTILE(INDEX(StressTableB313, MATCH(Cal!A7, 'Stress B31.3'!MaterialB313, 0), 0), 1 - PERCENTRANK('Stress B31.3'!$C$4:$AE$4, B7, 6))
Entia non sunt multiplicanda sine necessitate
To shg
Could you please upload the excel file? Thanks
Attached .
To shg,
Great. This formula works well too. Do I have to replace all blanks with (-9E307)? My actual Stress B31.3 sheet has many blank cells.
Thanks
What happens when you don't?Originally Posted by AyeNu
I should point out that this takes about two seconds to do. Select the table, and do Ctrl+G (that's Go to), press Special, tick Blanks, press OK. Then paste -9e307 in the formula bar, press and hold the Ctrl key, then press Enter.
To shg,
Thanks again.
And any advice for using new functions of PERCENTILE and PERCENTRANK? because these functions may not be available in future versions of Excel.
Neither may SUM. But they are available now, and that's what we have to work with.... because these functions may not be available in future versions of Excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks