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 livioflores-ga on 12 Mar 2005 21:23 PST

Hi!!

Let me show you if I understood your problem:
You have an Excel spreadsheet that has a list of about 25,000 pieces
with like this:


       |       A           |       B         |        C
_______|___________________|_________________|__________________
1      |   Part number     | Supplier Price  | Converted Prices
2      |     00001         |        8        |       8*3.2
3      |     00002         |       32        |      32*2.7
       |      ...          |      ...        |       ...
       |     ...           |     ...         |      ...
25009  |     25008         |      284        |     284*1.8


And you need a formula for column C (Converted Prices) that make the
Converted prices values being calculated automaticlly according to the
Supplier Price. That is if the supplier price is in the range from 75
to 150 (say 100 euros) the Converted price must be 250 euros, and this
value must be calculated inmediately after you input the Supplier
Price data in an automated fashion.

One more thing that must be clarified before I give you the answer is
the conversion criteria:
The limits of each criterion are ambiguous, for example for a part
with a supplier price of exactly 150 euros which conversion factor
must be used. You must clarify this to get an accurate answer.

Regards.
livioflores-ga

---

Clarification of Question by livo11-ga on 13 Mar 2005 01:52 PST

Hi livioflores-ga,

Thanks for the quick response.

You have correctly understood what I want to do in the first part of your query.

For the second part, if the original value is exactly 150, then I
would like the calculation to be the calculated by the next criterion.

Hope that clears everything up.

Thanks again,

livo11-ga

Hi livo11!!


The formula that you need is a series of nested IF logical operations
that determine the proper factor to be used for conversion.
You must use also the AND operator. The AND operator has the following syntax:
=AND(condition1,condition2?) and returns the value TRUE if all of its
arguments are true, otherwise it returns the value FALSE.

The IF function has the following syntax:
=IF(ConditionToTest,WhatToDoIfConditionIsTrue,WhatToDoIfConditionIsFalse) 

The first IF question is for the first criterion, etc. For example if
you have the Supplier Prices in the column B:

=IF(B2<10,B2*3.2,"Do other thing")

The above formula check for the value in B2 and if it is less than 10
give us the value (B2*3.2) using the proper factor, in the negative
writes Do other thing in the cell.
If the value in B2 (the supplier price value) is greater or equal than
10, other criterion must be used, you can nest another IF operator to
get:

=IF(B2<10,B2*3.2,IF(AND(10<=B2,B2<75),B2*2.7,"Do other thing")

If B2=24, the above formula give us the value (24*2.7)=64.8, if B2=142
writes Do other thing in the cell, if B2=5 give us the value
(5*3.2)=16.


You must nest all the IF operators until you cover all the
possibilities, in this case you will get the following formula:

=IF(B2<10,B2*$F$2,IF(AND(10<=B2,B2<75),B2*$F$3,IF(AND(75<=B2,B2<150),B2*$F$4,IF(AND(150<=B2,B2<200),B2*$F$5,IF(Y(200<=B2,B2<250),B2*$F$6,IF(AND(250<=B2,B2<300),B2*$F$7,IF(AND(300<=B2,B2<400),B2*$F$8,B2*$F$9)))))))

Note: to make the formula more flexible I wrote the conversion factors
in the column F (cells F2 to F9), so if in the future the factors
change you only need to change it value there not in each row.
The $ signs used means an absolute cell reference, this is useful when
you write a single formula will be applied to a range of cells (like
in this case) and you want that some cell references remain the same.
For example:
Row Absolute ( =A$1 ) - Relative column reference and Absolute row
reference. The row number always remains the same when you copy the
formula.
Column Absolute ( =$A1 ) - Absolute column reference and Relative row
reference. The column letter always remains the same when you copy the
formula.
Asolute (=$A$1) - Absolute column reference and Absolute row
reference. The column letter and the row number always remain the same
when you copy the formula.

The formula that I gave you must be posted in the cell C2 (for this
example) and then must be copied to the full range of listed parts
(cells C3 to C25000, etc.)

You can download a simple sample spreadsheet with 10 parts to see how
the formula works. I do not know how the formula syntax will be
displayed in your Excel window because I am using an Spanish version
of Excel and the syntax is different (SI instead IF, Y instead AND and
';' instead ','); but based in my own experience the syntax is
automatically converted to the correspondent of the current version in
use.
Here is the sample link:
Try changing the Supplier Prices values to see how the formula works. 
http://www.geocities.com/artistaflores/Converted_Prices.xls


For references see the following pages:
"Excel Logical Functions":
http://www.dcs.bbk.ac.uk/~galitsky/ITC/logifun.pdf

"Microsoft Excel Tutorial - Lesson 14: Logical Functions":
http://www.functionx.com/excel/Lesson14.htm

"Excel Formula Function - How do I do nested IF / THEN statements in Excel?":
http://www.ozgrid.com/Excel/excel-nested-if-function.htm

"How to Protect your Formulas":
Protecting your formulas can prevent accidental editing and viewing.
http://www.bettersolutions.com/excel/121019241833311116212151962407/651512222117229104212151962407.htm



I hope that this helps you. Feel free to request for a clarification
if you have a trouble with the download or if you you find something
unclear. I will be glad to give you further assistance on this topic
if you need it.

Best regards.
livioflores-ga

---

Request for Answer Clarification by livo11-ga on 13 Mar 2005 10:38 PST

livioflores-ga!

What can I say?  I have just read through your response for the first
time and of course, it is right now a Holy Grail to me, I just need
time to work through it to understand it better myself.

Having said that, I am eternally grateful for the time and effort you
have made with this and for the mightily quick response.

This is not a request as yet but a sign and vote of thanks for a job
well done.  Please check back for any other queries I may have in the
near future i.e. over the next two to three days.

Your a star...

Livo11

---

Clarification of Answer by livioflores-ga on 13 Mar 2005 11:48 PST

Hi!!


Thank you very much for the comments, the good rating and the generous tip.
It was a pleasure to work in your question, and of course I will
gladly clarify your future queries related to this question, I will
keep an eye on it :>).

Sincerely,
livioflores-ga

---

Request for Answer Clarification by livo11-ga on 13 Mar 2005 23:19 PST

livioflores-ga!

I have a problem with accessing the example you have posted @
geocities, the operation times out when I click the link.  I am in
Beijing at the moment so this maybe the problem as China blocks many
sites it judges as having unfavourable content.  Is there some other
way we can share this example between us?

Additionally;

I can follow this part of your explanation;

=IF(B2<10,B2*3.2,IF(AND(10<=B2,B2<75),B2*2.7,"Do other thing")

But cannot see how the complete formula you posted in column F would
have the corresponding 'what to do' like this;

B2*$F$3 or B2*$F$4

My query being, where is the conversion factor 'what to do' (i.e. *3.2
or *2.7 or *2.5) in the formula?  Possibly this is just an example
conversion factor and I must substitute the '3' or '4' values shown
above for my particular calculation factors...?

Of course, it is much better I see the sample speadseet.

Please be patient while entertaining a fool!

Thanks,

livo11

---

Clarification of Answer by livioflores-ga on 14 Mar 2005 06:42 PST

Hi!!

I trie dto download the file from different computers and have no
problem (all in Argentina), so yes, it could be a Chine problem
Unfortunately a direct contact through email between you and me is
against the policies of Google Answers, so the easy way to send you
the file is closed.
You can send an email to a friend out of China telling him the file's
link, he could download the file and send it to you via email.
What I can do is to give you the list of cell inputs, you just copy
and paste the inputs in a blank sheet to get the example:

The Row 1 is for titles.
Column A: This is the part numbers column. You can use real part
numbers, they don't take part in calculations.
A1: Part Number
A2: 1
A3: 2
A4: 3
...
A11: 10

Column B: this is the supplier prices column, you can put the values that you want.
B1: Supplier Prices
B2: to B11: the prices value that you want

Column C: Here you must input the formulas to calculate the Retail prices.
C1: Converted Price
C2:=IF(B2<10,B2*$F$2,IF(AND(10<=B2,B2<75),B2*$F$3,IF(AND(75<=B2,B2<150),B2*$F$4,IF(AND(150<=B2,B2<200),B2*$F$5,IF(Y(200<=B2,B2<250),B2*$F$6,IF(AND(250<=B2,B2<300),B2*$F$7,IF(AND(300<=B2,B2<400),B2*$F$8,B2*$F$9)))))))

After the above formula be input in the C2 cell, copy and paste it to
the cells C3: to C11:

Column D: leave it blank.

Column E: (all the values here are texts no numbers)
E1: Supplier Price Range
E2: 0 to 10
E3: 10 to 75
E4: 75 to 150
...
E10: 300 to 400
E11: 400 or more

Column F: the conversion factors column.
F1: Convesion Factor
F2: 3.2
F3: 2.7
F4: 2.5
F5: 2.3
F6: 2
F7: 1.8
F8: 1.6
F9: 1.5

Thats all that you need to make the sample yourself.


Regarding to your query about the formula and column F values ($F$3),
after see the instructions above may be you caan see it more clearly,
in the complete formula I use the inputs in column F to make the
formula more flexible, in the case that you need to change the
conversion factors in the future. You can replace my formula by this
one:

=IF(B2<10,B2*3.2,IF(AND(10<=B2,B2<75),B2*2.7,IF(AND(75<=B2,B2<150),B2*2.5,IF(AND(150<=B2,B2<200),B2*2.3,IF(Y(200<=B2,B2<250),B2*2,IF(AND(250<=B2,B2<300),B2*1.8,IF(AND(300<=B2,B2<400),B2*1.6,B2*1.5)))))))

If you use this formula you do not need the column E and F inputs and
you can leave them blank. But when you change any factor in the future
you must modify the formula in the cell C2: and then copy and paste it
again to the rest of the cells in column C.

I hope that this clarify your queries; please do not hesitate to
continue using the clarification feature all times you need it.

Regards.
livioflores-ga

---

Request for Answer Clarification by livo11-ga on 14 Mar 2005 09:23 PST

Hi livioflores-ga!

Phew, we have done it!  I have had to make a few changes in the syntax
of the formula due to the demands of the supplier and the format that
they would like to have the file uploaded but it DOES work!  And by
the way, I now fully understand everything that I initially queries
after your first answer.  The formula now looks like this;

=IF(B1<10;B1*$F$1;IF(AND(10<=B1;B1<75);B1*$F$2;IF(AND(75<=B1;B1<150);B1*$F$3;IF(AND(150<=B1;B1<200);B1*$F$4;IF(AND(200<=B1;B1<250);B1*$F$5;IF(AND(250<=B1;B1<300);B1*$F$6;IF(AND(300<=B1;B1<400);B1*$F$7;B1*$F$8)))))))

Thank you very much for all of your help, you have saved me a lot of
hard work and anguish.  Maybe one day I may be able to employ somebody
to do all of this for me!

Thanks again!

livo11-ga

---

Clarification of Answer by livioflores-ga on 14 Mar 2005 19:37 PST

Hey!! I am very glad to receive such goods news from you. A well done
job is always a pleasure for us.
Regarding the syntax modifications, yes, depending on the language
version Excel uses ',' or ';' as arguments' separation. And other
minor synthax differences appear.
Until you find a good employee that do all the hard work for you, we
will be here to give the best of us to help you.

Best regards.
livioflores-ga

Fandabidozi!

Star answer, star person.  This was a quick response with a well
documented reply and solution.  Oh how I wish I had gone to school!

Thank you livioflores, I wish we had more in common than just a few
letters in our names...

Livo11

Okay, well, I see you've got your solution; but I'll post my comments
anyway, in hopes that it may help you or others in some way.  I would
severely hesitate to use such a drastic solution.  Such a formula is
not needed, period.  Too long, too many IF's (we're only allowed 7
nested).  From your example above, you only need the following in C2,
then copy down as far as you need ...

=B2*IF(B2<0,1,LOOKUP(B2,{0,3.2;10,2.7;75,2.5;150,2.3;200,2;250,1.8;300,1.6;400,1.5}))

This should suffice.  You can add/subtract from this as needed, plus
it will be MUCH easier to negotiate than such a convuluted (imho) IF
scenario.  The one IF function here is only to check for a value less
than zero.  If this is not going to happen, you can just take it out,
and be that much shorter on your formula.  One less computation step
for Excel to make.

Best regards,
Zack Barresse