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.

On Thu, 4 Sep 2008 13:40:02 -0700, WCO wrote:

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

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 ?

On Fri, 5 Sep 2008 05:38:01 -0700, WCO wrote:


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

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" wrote:

On Fri, 5 Sep 2008 05:38:01 -0700, WCO wrote:


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

On Fri, 5 Sep 2008 11:15:07 -0700, WCO wrote:

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),MAT CH(quantity,row_of_quantity_values))

--ron