+ Reply to Thread
Results 1 to 5 of 5

IF Code help for price list. Price groupings cell allocation.

  1. #1
    Registered User
    Join Date
    08-20-2010
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Cool IF Code help for price list. Price groupings cell allocation.

    Hi, please can someone help with a formula to do the following? I am doing a price list for kitchen cabinets and the doors for these cabinets fall into price groups. I have worked out averages for all the doors and divided a value from and to into groups:

    0-13 group 1
    14-20 group 2
    21-27 group 3
    28-34 group 4
    35-41 group 5
    42-48 group 6
    49-55 group 7
    56-63 group 8

    What I need to do is if the average of a certain set of doors is 36 then I nee the a cell to display GP5, 21 display GP3 etc etc...

    Please be patient with me I am a beginner with Excel but am very keen to learn more. Great piece of software :-)

    Thankyou for your help.
    Last edited by hotwoz; 08-20-2010 at 05:49 AM. Reason: Title Change

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: IF Code help for price list. Price groupings cell allocation.

    Maybe try =IF(A1<=13,"GP1","GP"&int(A1/7)) where A1 has the number of sets
    Last edited by arthurbr; 08-20-2010 at 07:05 AM.

  3. #3
    Registered User
    Join Date
    08-20-2010
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: IF Code help for price list. Price groupings cell allocation.

    Quote Originally Posted by arthurbr View Post
    Maybe try =IF(A1<=13,"GP1","GP"&int(A1/7)) where A1 has the number of sets
    Thankyou, I understand the formula up to a certain point. If A1 is less than or equal to 13 then input GP1. Can you explain in words the remainder of the formula. I have the formula working on GP1 up to the point on the formula I understand.

    Thanks

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: IF Code help for price list. Price groupings cell allocation.

    The lower limit of each of your groups is a multiple of 7. So dividing by 7 and taking the integer part of the division gives the group number.
    It is not so for GR1 since this one spans 14 possibilities (0-13), the others always span 7

    Tools- formula Auditing - Evaluate formula will show you how it works

    And the "gp"& part is just another concatenation method

  5. #5
    Registered User
    Join Date
    08-20-2010
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: IF Code help for price list. Price groupings cell allocation.

    I think your formula is a little advanced for me, or I'm dumb. So I did a bit of research based on the first bit of your formula that I did understand and came up with something a little long winded but it seems to do the job.

    =IF(P44>($R$50),"POA",IF(P44>($R$49),"GROUP 8",IF(P44>($R$48),"GROUP 7",IF(P44>($R$47),"GROUP 6",IF(P44>($R$46),"GROUP 5",IF(P44>($R$45),"GROUP 4",IF(P44>($R$44),"GROUP 3",IF(P44>($R$43),"GROUP 2",IF(P44<=($R$44),"GROUP 1")))))))))

    I put the variable group numbers in a seperate box so that when the prices rise I can just increase them by the percentage. I'm sure you think this is a long winded way of doing things but its a revelation to me :-)

    Thankyou for your help, it would have taken me far longer to get where I am now without your assistance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1