Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Operators in a Table (97-SR1)

    Greetings all. I imagine I will be posting a couple of queries, but one step at a time.
    My brain has taken a complete departure today and I can't think of a easy workaround.

    I have a product table with a pricing structure. I need to easily calculate the pricing on and return that price to another sheet.
    Column A B C
    Code Size Price
    105 <=20 $30
    105 >20 $1 (For each one more than $20, add $1 to total)
    106 <=10 $25
    106 >11<=30 $30
    106 >30<=50 $35
    (for each value, calculate individual price)

    107 <=10 $10
    107 >11<=20 .95
    107 >20<=45 .85
    (again for each one - caculate).

    For column B, where I have the Size, it shows as text naturally. Rather than calculate line by line, is there any way to reference this pricing table on another sheet to say that when Product 106 is purchased with 27 units, the price per unit will calculate out to $30?

    Like I said, complete brain freeze. Unless I create some other sub-unit code and then add on another variable to the equation to differentiate the size variances, is there any other way rather multitude if statements to figure this out?

    Your thoughts are appreciated. Thanks.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Operators in a Table (97-SR1)

    I do not know if this is exactly what you wanted, but if you were to split the ranges up so that there is a product, maximum, minimum, base cost for rnage and cost per unit column the result is givven by the array formula =SUM((Product=Code)*(Size>=Min)*(Size<=Max)*(Base+ Cost*Size))

    refer to the attached for an example
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Operators in a Table (97-SR1)

    An Array formula! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    I haven't used array formulas too much but I think that will fit the bill perfectly (after some tinkering of the tables).
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •