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

1. ## 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 :-)

2. ## 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

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

Originally Posted by arthurbr
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. ## 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. ## 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.