help with a formula pls

[Norman Harker]

Hi Keevil!

With your table of rates in (e.g) H1:I4:
1 50
10 55
25 62
30 67

Try:
=VLOOKUP(A1,$H$1:$I$4,2)
Returns the appropriate rate for the quantity in A1 from the second
column of the table.

If the table is on another sheet, you need to name it:

=VLOOKUP(A1,MyRates,2)

You also need to be a bit more disciplined with your borders between
rates. (i.e. you give 55 and 62 for 25 pages)

--
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.

 

[Niek Otten]

You obviously didn't try! Works as you intend.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Norman Harker]

Hi Keevil!

It looks to me that you just have to set your VLOOKUP table correctly:

I have a table in H1:I6

1 80
19 90
24 100
29 120
34 150
39 160

My formula is now:

=VLOOKUP(A1,$H$2:$I$6,2)

VLOOKUP's algorithm is to look up the value up to but not exceeding a
particular value in the first column

So for your example of 34, the value of 150 will be returned.

Pages of 30-34 will be 150

--
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.

 

[Norman Harker]

Hi Keevill!

No problems! Great to hear it's working OK

--
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.

 

[-keevill-]

I have made a worksheet to calculate the profit for each days sales of
different publications. The cost for each days publication is different each
day according to the number of pages it contains.( printing costs mainly ) .
I charge the same each day for each particular paper. There are several
different ones each day. I have made a worksheet in the workbook with a
couple of columns with the costs based on no of pages.
Something like
10-25 pages 55
25-30 pages 62
30-35 pages 67
etc .
What I want to do is to input the number of pages on any given day into a
cell in the main worksheet and pull automatically the cost from the sheet
containing the above. Right now, I just manually link the cell containing
the appropriate cost to the sheet which does the main calculations

 

[-keevill-]

Hi and thx for the help here. I don't think that this will achieve what I
want but perhaps you will assist some more.
You see I want to input the number of pages into a cell and then look up the
value according to the number of pages in my 'subtable'.
So for example, If I input 34 pages as the number in A1, then it should
search the number of pages/ rates table and come up with the cost amount.
So if my rate table is like thus.
20 100
25 120
30 140
35 150
40 160

Then the formula would return the value of 150 because 35 is the nearest
rate for the number of pages.
I can of course put in rates for every number of pages into this table if
required.
like so
20 100
21 100
22 101
23 101
24 102

etc.

 

[-keevill-]

Guys thx for your patience !!!
It works just fine.
-keevill-