Looking for a formula

[WCO]

I need to see if it is possible to create a formula that will look for 2
variants to pull a range of 21 prices. For example if square foot is between
24-39 and qty=1 then it would pull a price of 10.00. This formula would
include 7 different square footage possibilities, 3 different quantity
options, for a total of 21 different price possibilities.

I wasnt sure if i could put the info on another tab and have the formula
pull from there or if it was easier to create a straight formula for it. Any
ideas are greatly appreciated.

 

[Ron Rosenfeld]

I need to see if it is possible to create a formula that will look for 2
variants to pull a range of 21 prices. For example if square foot is between
24-39 and qty=1 then it would pull a price of 10.00. This formula would
include 7 different square footage possibilities, 3 different quantity
options, for a total of 21 different price possibilities.

I wasnt sure if i could put the info on another tab and have the formula
pull from there or if it was easier to create a straight formula for it. Any
ideas are greatly appreciated.

If you can mathematically describe the relationship between quantity, size and
price, you could certainly use a formula.

Or you could just set up a table and use a combination of INDEX/MATCH to do the
lookup.

You need to provide more information.
--ron

 

[WCO]

It is not important which method is used as long as i can get everything to
come up at the correct times. What sort of information do you need ?

 

[Ron Rosenfeld]

It is not important which method is used as long as i can get everything to
come up at the correct times. What sort of information do you need ?

The simplest would be the algorithm that you use to compute price given area
and quantity.
--ron

 

[WCO]

What i was looking for is that formula or algorithm. Oviosuly i would have to
fit it into my cells and such but a generic one to start off with so that i
have something to work with.

 

[Ron Rosenfeld]

What i was looking for is that formula or algorithm. Oviosuly i would have to
fit it into my cells and such but a generic one to start off with so that i
have something to work with.

I think we are not communicating.

*I* have no way of knowing what price you want to use for a combination of area
and quantity. If you are unable to provide that information, there is no way
for me to figure it out.

The only information you have provided is that for an area of 24-39 sq ft and
qty=1 the price is 10.00.

The formula for that would be

=if(and(area>=24,area<=39,qty=1),10)

This would not be an efficient method for the 21 variations you have, however.

As I wrote in my initial response, you could set up a TABLE, with area values
in the left-most column; quantity values in the top row; and prices in the
table -- and then use index/match to find the correct combination.

In general, that formula would look like:

=INDEX(TABLE,MATCH(area,column_of_area_values),MATCH(quantity,row_of_quantity_values))

--ron

 

[WCO]

Ron,

I appreciate your help. Below are the numbers i am trying to get loaded

if Qty1
24-39 12.25
40-49 11.75
50-79 10.75
80-99 10.32
100-119 10.21
120-239 10.09
240+ 9.50

if qty 2-26
24-39 11.27
40-49 10.81
50-79 9.89
80-99 9.49
100-119 9.41
120-239 9.32
240+ 8.78

if qty 26+
24-39 10.78
40-49 10.34
50-79 9.46
80-99 9.08
100-119 9.01
120-239 8.94
240+ 8.42

I have setup a spreadsheet with the following headers.

Square Foot Quantity

My idea was that they could enter the square footage, then the Quantity and
it could figure in the price that was needed for that combination. Does this
give you all the info you need ?

 

[Ron Rosenfeld]

Ron,

I appreciate your help. Below are the numbers i am trying to get loaded

if Qty1
24-39 12.25
40-49 11.75
50-79 10.75
80-99 10.32
100-119 10.21
120-239 10.09
240+ 9.50

if qty 2-26
24-39 11.27
40-49 10.81
50-79 9.89
80-99 9.49
100-119 9.41
120-239 9.32
240+ 8.78

if qty 26+
24-39 10.78
40-49 10.34
50-79 9.46
80-99 9.08
100-119 9.01
120-239 8.94
240+ 8.42

I have setup a spreadsheet with the following headers.

Square Foot Quantity

My idea was that they could enter the square footage, then the Quantity and
it could figure in the price that was needed for that combination. Does this
give you all the info you need ?

I would recommend you use the Table method I described before -- just fill in
the table with your values.

For example:

Set up Names:

Qty =Sheet1!$L$1:$O$1
SqFt =Sheet1!$L$1:$L$8
Tbl =Sheet1!$L$1:$O$8

Put this table in L1:O8

SqFt\Qty 1 26 27
24 12.25 11.27 10.78
40 11.75 10.81 10.34
50 10.75 9.89 9.46
80 10.32 9.49 9.08
100 10.21 9.41 9.01
120 10.09 9.32 8.94
240 9.5 8.78 8.42

Then with

A2: square footage entry cell
A3: quantity entry cell

Use this formula to obtain the proper price:

=INDEX(Tbl,MATCH(A2,SqFt),MATCH(A3,Qty))

--ron

 

[WCO]

OK Ron, Thanks for the help there is just one thing im not sure about.

Where do i load the Names info/formula ?


Qty =Sheet1!$L$1:$O$1
SqFt =Sheet1!$L$1:$L$8
Tbl =Sheet1!$L$1:$O$8

 

[Ron Rosenfeld]

OK Ron, Thanks for the help there is just one thing im not sure about.

Where do i load the Names info/formula ?


Qty =Sheet1!$L$1:$O$1
SqFt =Sheet1!$L$1:$L$8
Tbl =Sheet1!$L$1:$O$8

It varies depending on the version of Excel you are using. Probably best to
look up how to name ranges in HELP.
--ron

 

[Ron Rosenfeld]

OK Ron, Thanks for the help there is just one thing im not sure about.

Where do i load the Names info/formula ?


Qty =Sheet1!$L$1:$O$1
SqFt =Sheet1!$L$1:$L$8
Tbl =Sheet1!$L$1:$O$8

If naming ranges is too difficult, just use the cell references in their place
in the formulas.
--ron