I have a large excel file (nearly 25,000 pieces) of part number prices
from my supplier in Austria listed by part numbers.

I wish to convert these prices into my dealer and retail prices
according to certain criteria as follows;

Euro 0 to 10 x 3.2
Euro 10 to 75 x 2.7
Euro 75 to 150 x 2.5
Euro 150 to 200 x 2.3
Euro 200 to 250 x 2
Euro 250 to 350 x 1.8
Euro 300 to 400 x 1.6
Euro +400 x 1.5

Is there a formula that I can use to make this calculation without
having to work through the whole file row by row?  So far I have only
been able to make an array for cells falling into the same price range
and nested together as follows;

{=A1:A24*3.2}

The file must be kept in the same order for later upload to the
company website that is used for my employees and dealers to make
orders and that will display both the dealer and retail prices.

---

Request for Question Clarification by pafalafa-ga on 13 Mar 2005 16:45 PST

livo11-ga,

I created an Excel mock-up and tried xarqi-ga's formula that was
offered in the comments below, and which I'll repeat here:


=IF(A1>=400,A1*1.5,IF(A1>=300,A1*1.6,IF(A1>=250,A1*1.8,IF(A1>=200,A1*2,IF(A1>=150,A1*2.3,IF(A1>=75,A1*2.5,IF(A1>=10,A1*2.7,A1*3.2)))))))


It does exactly what you asked in your question..."recognizes" the
size of the number, applies the appropriate multiplier, and puts the
answer in the cell that you have selected.

The only thing to note is that -- in your question -- the 'borderline'
values occupy two places, and are therefore ambiguous.  That is, for a
number like "10", your rules have it multiplied by 3.2 (in the 0-to-10
category) as well as by 2.7, (in the 10-to-75 category) since the
number is repeated in each category.


The formula above treats each borderline value as belonging
exclusively to the higher-value category -- 10 is multiplied by 2.7,
not 3.2.  200 is multiplied by 2, but not 2.3, and so on.

If that's an issue for you, let me know, and the formula can certainly
be modified to meet your needs.

I can also post my test file for you to view, if that is of interest.

But the main question I have for you at this point is...Do you have
everything you need...is your question answered?  Or is there anything
else a researcher here can do for you make for a complete answer to
your question?

Let us know.

pafalafa-ga

---

Request for Question Clarification by pafalafa-ga on 14 Mar 2005 06:53 PST

livo11-ga,

Glad to hear that the information we've provided thus far is getting
you close to a solution.

Let's see if we can get you 100% of the way there.

You want to take the formula you have now and copy it down the length
of a column so that it not only repeats (about 25,000 times) but also
self-adjusts to perform the arithmetic on the appropriate column.  In
Excel, this is known as "relative copy".

Try this:

--go to the cell with the version of the formula that you want to copy
to the cells below

[e.g.  If your formulas appear in column C, and you already have valid
formulas in C1 through C10 -- and need to fill in C11-C25000 then
click on cell C10]

--A dark border should appear around the cell, with a "smart tag" in
the lower right-hand corner.

--Move your cursor over this smart tag -- the cursor will change from
its normal appearance to a plus-sign (+) when it is properly
positioned.

--Click-and-hold on the smart tag.  That is, click your mouse down,
but don't release is.

--Drag the cursor all the way down to the final position.
[NOTE...it actually doesn't take all that long to drag, even for
25,000 cells.  The further down the page you move your cursor, the
faster the drag will go...when you start approaching the end of the
spreadsheet, move the cursor higher up on the page to slow down the
scrolling]

--Release the mouse button.  Your formula should then be pasted, with
a relative copy, with all the proper adjustments made.


This is a fairly straightforward process, but if you haven't done it
before, it takes a bit of getting used to the right movements.  I hope
I've described them to you clearly here.

Let me know if this works, and if you have everything you need, or if
you need any additional information.

Best of luck.

pafalafa-ga

livo11-ga,

That's great news...glad to hear that your little Excel problem worked out so well.

Let us know if there's anything else we can do for you, now or in the future.


pafalafa-ga

Assuming your prices are in column A:
Paste the following formula into a cell in at the end of the row for
the first part:
=IF(A1>=400,A1*1.5,IF(A1>=300,A1*1.6,IF(A1>=250,A1*1.8,IF(A1>=200,A1*2,IF(A1>=150,A1*2.3,IF(A1>=75,A1*2.5,IF(A1>=10,A1*2.7,A1*3.2)))))))

Then select this row, and all the 25000+ rows below it (select it,
then shift click on the last cell), and do a "Fill Down" from the edit
menu.

That also assumes your first part row is row one.  Wherever the first
part price happens to be, just use its cell address in place of "A1"
in the given formula before you do the copy down.

At first read, this seems really easy. 

Sheet 1 contains the parts. Sheet two contains a named range looking
something like this:

0	3.2
10	3.2
75	2.7
150	2.5
200	2.3
250	2
350	1.8
400	1.5

The adjusted price column is then a vlookup of your named range:

=vlookup(source cell, named range, 2, true)* source cell

Say your range is named multiplier and you're checking the euro price
in cell d2. The formula would look like
=vlookup(d2,multiplier,2,true)*d2.

But then, I could be missing something.....

Hi xarqi-ga & pafalafa-ga!

Thanks very much for your responses, it is a great help.

I have tried the formula on a mock-up as did pafalafa-ga and had to do
some syntax adjustments as the original file requires ',' as the
decimal for the numbers and prices.  Additionally, the supplier price
is in column 'B'.  However it has worked and now looks like this;

=IF(B2>=400;B2*1,5;IF(B2>=300;B2*1,6;IF(B2>=250;B2*1,8;IF(B2>=200;B2*2;IF(B2>=150;B2*2,3;IF(B2>=75;B2*2,5;IF(B2>=10;B2*2,7;B2*3,2)))))))

I am however having a problem with filling down the formula to the
remaining rows correctly.  Following the instructions of 'select row 1
then shift-click on the last row followed by edit menu and fill down'
has only substituted the information in row 1 to all of the remaining
rows loosing the original data contained therein.

Am I doing something wrong here? Or, is there not a method to make the
formula an array such as this;

{=B1:B2500;IF(>=400;*1,5;IF(>=300;...)))} and so on?

I am nearly there my friends.

Thanks again,

livo11-ga

livo11-ga,

I added a note in the Clarifications section (above)...have a look and
let me know if everything works out OK.

paf

Yaaargh - oops - I made a typo!

Don't select the whole ROW for the fill-down operation, just select
the CELL with the new formula in it, and all the empty cells below it,
then do the fill down.

My profound apologies.

This has been a complete success!

Thanks very much to everybody who has helped me on this, I was in
danger of my hair going prematurly grey, but not any more thanks to
you guy's!

Keep up the good work,

Ciao,

livo11-ga

A big "THANKS" to xarqi-ga, not only for kicking off the process, but
for sticking with it to the end.  A real professional job.

paf