Formula problem 1

[Cassaro]

I am hoping someone can help me figure out a formula for a worksheet I am working on. I am looking to do a simple mutliplication task that uses different mutliplication factors for different quantity ranges.

Lets say that if I enter a quantity in cell B1 between the number 1-1000 it will use a mutliplication factor of 1.871 entered in cell D1. But, if the qunatity entered is between 1001-2000 it will use the mutliplication factor of 1.316 entered in cell D2.

I have 13 different mutliplication factors that I need to use and I cannot figure out how to get the formula to look at the quantity entered and use the correct mutliplication factor. It may be something easy that I am just missing or I could be looking to do the impossible.

Any help will be appreciated!

Chuck
tek-tips@aspenpkg.com

If you feel a post has been helpful to you, click on the link at the bottom of their post to let them and others know the information was of use.

 

[vbMax]

You can use a nested "if" statement. A shorter,
simple example would be:
=IF(A1 < 100, A1 * B1, IF(A1 < 200, A1 * C1, IF(A1 < 300, A1 * D1, &quot;Out of range&quot))

- vbMax

 

[mscallisto]

This can of course be done with &quot;=IF&quot; statements but is there a better (more efficient) way?

For instance, is there a mathmatical relationship between the your factors and ranges?

Will the Factors or ranges change, etc?

If we understand exactly what you need we can supply the best method to achieve the desired results.

Much Luck

 

[ETID]

How about a vlookup table in another sheet to return the correct factor?

 

[mwolford]

Using the following IF formula, you can do 8 different rates, but because you can only nest 7 IF functions, I don't know if a way to do 13.

=IF(A4>10000,A4*C4,IF(A4>9000,A4*C5,IF(A4>8000,A4*C6,IF(A4>7000,A4*C7,IF(A4>6000,A4*C8,IF(A4>5000,A4*C9,IF(A4>4000,A4*C10,IF(A4>3000,A4*C11,A4*C12))))))))

(the amount is in A4 and the rates are in C4:C12)

 

[tsuji]

Hello, Cassaro.

The lookup table is designed to suit this type of job, for sorted and graded numerical partitioning.

Your posing of the problem, the D2 in particular, has me an idea of what you're looking for. But I can be wrong in this aspect.

[1] At some freely available area, construct the lookup table. I set it to three columns, say F1:H14, with F1:H1 as heading and F2:H14 the 13 gradings.
F1: Range
G1: Multiplier
H1: RowOffSet
[2] (H2,H3,...,H14) are (0,1,...12)
which are the offset of row of Dn n=1,2,...,13 wrt B1 data entry cell.
[3] (G2,G3,...,G14) are (1.871,1.316,...)
which are your multiplicative factors.
[4] (F2,F3,...,F14)=(1,1001,2001,...)
which are the lower-bound of the grid.
[5] B1 is the cell of data input.
[6] D113 are where appear eventually a number with B1 mulitplied by the factor and placed on a cell reflecting the grading.
[7] The formula for D1
D1: =If(vlookup($B$1,$F$2:$G$14,3)=(row()-row($B$1)), vlookup($B$1,$F$2:$G$14,2)*$B$1,&quot;&quot
Then copy D1 formula down to D213.

The result is what you are looking for.

regards - tsuji

 

[tsuji]

<Erratum>

Just a correction to an unintentional mistake in the formula of D1 and hence D213, which should be obvious from the construction.

D1: =If(vlookup(...$F$2:$G$14...$F$2:$G$14...)

should be read as
D1: =If(vlookup(...$F$2:$H$14...$F$2:$H$14...)

where the range stops on H rather than on G column.

- tsuji

 

[Cassaro]

Thank you all for your replys.

Tsuji,

You seam to have found exactly what I am looking to do. The &quot;IF&quot; statement would have worked for me except for the fact that I have 13 different factors to deal with. I have never set up a lookup table but I'm now off to give it a try

Thanks again....and I may be back Chuck
tek-tips@aspenpkg.com

If you feel a post has been helpful to you, click on the link at the bottom of their post to let them and others know the information was of use.

 

[ETID]

Hmmmm...now where did I hear that suggested before?

 

[Cassaro]

ETID,

Thank you as well. Your post suggested using the vlookup table which was right on target, but without some direction I was unable to set it up because I have never done one before. Tsuji was kind enough to take the time to walk me through setting up that table step by step.
I find myself doing the same thing when I go to answer a question. Sometimes I assume that the person I am replying to knows what I am talking about. We all have to remember that we are all at different skill levels here and sometimes a very basic step by step instruction is the best answer.
Again I wish to thank EVERYONE for their answers.

Chuck
tek-tips@aspenpkg.com

If you feel a post has been helpful to you, click on the link at the bottom of their post to let them and others know the information was of use.

 

[tsuji]

Cassaro,

Thank you for your kind words.

Hello, ETID,

I have no doubt you must be very knowledgable, in Excel at least. I remember I've encountered your response here and in another thread. In last case, you missed the point. Here again, you are again off the point on the construction of what in my posting at D113. I would encourage you to continue to post better postings. I sure am here be an eager reader.

regards - tsuji