IF Statement

[Guest]

I am attempting to use nested IF statements to filter out the correct answer from a table, but I need more IF statements than Excel offers. What else can I do? Here is the table

Base US No 2 Diesel
Retail Sales by All Sellers(C/gal) US No 2 Diesel Retail Sales
by All Sellers Adjustment Ranges(C/gal) Percent Change in Base Product Freigh
140.0 150-154.9 1
155-159.9 2
160-164.9 3
165-169.9 4
170-174.9 5
180-184.9 6

The data I want to sort is in range M20:M36

The website I need to pull the comparison raw data from is http://tonto.eia.doe.gov/oog/ftparea/wogirs/xls/psw18vwall.xls using the weekly data. The information I need is in column B from August 25, 2003 to present

Once I have this data in the spreadsheet, I want to take monthly averages and compare that average number with the above table to determine the % change in the base product freight rate that needs to be applied to the contract

 

[Norman Harker]

Hi Neal!

You have data missing:- 175-179.9

But here's the basics:
K1:L6
140 1%
150 2%
155 3%
160 4%
170 5%
180 6%

Formula:
=VLOOKUP(M20,$K$1:$L$6,2)
Copy down.

One of the beauties of VLOOKUP is the ability to edit the table looked
up. If you add a row, don't forget to edit the formula.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I am attempting to use nested IF statements to filter out the

correct answer from a table, but I need more IF statements than Excel
offers. What else can I do? Here is the table.

Base US No 2 Diesel
Retail Sales by All Sellers(C/gal) US No 2 Diesel Retail Sales
by All Sellers

Adjustment Ranges(C/gal)
Percent Change in Base Product Freight

140.0 150-154.9 1%
2%
3%
4%
5%
6%


The data I want to sort is in range M20:M36.

The website I need to pull the comparison raw data from is

http://tonto.eia.doe.gov/oog/ftparea/wogirs/xls/psw18vwall.xls using
the weekly data. The information I need is in column B from August
25, 2003 to present.

Once I have this data in the spreadsheet, I want to take monthly

averages and compare that average number with the above table to
determine the % change in the base product freight rate that needs to
be applied to the contract.

 

[Guest]

Thanks Norman

I just used the VLOOKUP function and I get a #NA error

What do you think I have done wrong

My range is b61:c6

The number in m20 is 159.9

the number I am looking to get back is 2%

----- Norman Harker wrote: ----

Hi Neal

You have data missing:- 175-179.

But here's the basics
K1:L
140 1
150 2
155 3
160 4
170 5
180 6

Formula
=VLOOKUP(M20,$K$1:$L$6,2
Copy down

One of the beauties of VLOOKUP is the ability to edit the table looke
up. If you add a row, don't forget to edit the formula
--
Regard
Norman Harker MVP (Excel
Sydney, Australi
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments
available free to good homes

I am attempting to use nested IF statements to filter out th

correct answer from a table, but I need more IF statements than Exce
offers. What else can I do? Here is the table

Retail Sales by All Sellers(C/gal) US No 2 Diesel Retail Sale
by All Seller

Adjustment Ranges(C/gal
Percent Change in Base Product Freigh

140.0 150-154. 1
2
3
4
5

http://tonto.eia.doe.gov/oog/ftparea/wogirs/xls/psw18vwall.xls usin
the weekly data. The information I need is in column B from Augus
25, 2003 to presentaverages and compare that average number with the above table t
determine the % change in the base product freight rate that needs t
be applied to the contract

 

[Norman Harker]

Hi Neal!

You need to check your table:

I'm making some guesses but I get a table in B61:B67

140 0%
150 1%
155 2%
160 3%
165 4%
170 5%
180 6%


With the VLOOKUP formula:

=VLOOKUP(M20,$B$61:$C$67,2)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.