Prev: insert a dash
Next: Removing Command Button
From: soma on 5 May 2010 14:09
i am trying to write a formula so that i can do the following from the table
WORKING PRESSURE BY ANSI CLASS (psig)
Temp (°F) 150 300 600 900 1500 2500
-20 285 740 1480 2220 3705 6170
100 285 740 1480 2220 3705 6170
200 260 680 1360 2035 3395 5655
300 230 655 1310 1965 3270 5450
400 200 635 1265 1900 3170 5280
500 170 605 1205 1810 3015 5025
600 140 570 1135 1705 2840 4730
650 125 550 1100 1650 2745 4575
700 110 530 1060 1590 2655 4425
750 95 505 1015 1520 2535 4230
800 80 410 825 1235 2055 3430
850 65 320 640 955 1595 2655
900 50 230 460 690 1150 1915
950 35 135 275 410 685 1145
1000 20 85 170 255 430 715
375 208 640 1,276 1,916 3,195 5,323
351 215 645 1,287 1,932 3,219 5,363

I am trying to match the numbers from last 2 rows starting from 2nd column
to the first row in the 2nd column.
For example if I get a value of 208 from a calculation then the formula
should return 150. but if i get less than 208 it should still return 150. and
if i get more than 208 the formula should go to the 1st row 3rd column and
display 300. and so on.
Last two rows in the table are calculated values from interpolation. all i
want is to assign a number from the first row based on the calculated value.
if any body knows any macro or any formula that i could use please help me.
many thanks
Soma
From: Tom Hutchins on 5 May 2010 14:40
Try
=IFERROR(INDEX($B$1:$G$1,MATCH(B17,$B$1:$G$1,1)),$B$1)
if you are using Excel 2007, or
=IF(B17<$B$1,$B$1,(INDEX($B$1:$G$1,MATCH(B17,$B$1:$G$1,1))))
with Excel 2007 or earlier.

Hope this helps,

Hutch

"soma" wrote:

> i am trying to write a formula so that i can do the following from the table
> WORKING PRESSURE BY ANSI CLASS (psig)
> Temp (°F) 150 300 600 900 1500 2500
> -20 285 740 1480 2220 3705 6170
> 100 285 740 1480 2220 3705 6170
> 200 260 680 1360 2035 3395 5655
> 300 230 655 1310 1965 3270 5450
> 400 200 635 1265 1900 3170 5280
> 500 170 605 1205 1810 3015 5025
> 600 140 570 1135 1705 2840 4730
> 650 125 550 1100 1650 2745 4575
> 700 110 530 1060 1590 2655 4425
> 750 95 505 1015 1520 2535 4230
> 800 80 410 825 1235 2055 3430
> 850 65 320 640 955 1595 2655
> 900 50 230 460 690 1150 1915
> 950 35 135 275 410 685 1145
> 1000 20 85 170 255 430 715
> 375 208 640 1,276 1,916 3,195 5,323
> 351 215 645 1,287 1,932 3,219 5,363
>
> I am trying to match the numbers from last 2 rows starting from 2nd column
> to the first row in the 2nd column.
> For example if I get a value of 208 from a calculation then the formula
> should return 150. but if i get less than 208 it should still return 150. and
> if i get more than 208 the formula should go to the 1st row 3rd column and
> display 300. and so on.
> Last two rows in the table are calculated values from interpolation. all i
> want is to assign a number from the first row based on the calculated value.
> if any body knows any macro or any formula that i could use please help me.
> many thanks
> Soma
 | 
Pages: 1
Prev: insert a dash
Next: Removing Command Button