Do i need a formula or macro to do this?

[jaugent27]

I have a spreadsheet that has about 1,500 products. Each product has a list cost, or Price Level 1, along with 9 more price levels, each with a discounted price. I need to create a column that determines the discount % from Price Level 1. Since each product has 11 rows (not sure why the doc has a last row with no price level listed but that's how it is), I can't simple copy the formula down and expect it to find the Level 1 price each time. I'm sure this can be done with an IF statement, IF column A says "Price Level 10" do this formula, but it would be tricky to then have it calculate Price Level 1 price for each one unless there's a way to say 'go two rows up' depending on which price level you're at. It seems the doc is consistent in how the price levels are listed 1, 10, 3-9, 2. I would also assume it's easiest to do this with a macro, but I've never done that so wouldn't even know where to start. Hopefully one of the Mr Excel ace's can help out. I really appreciate it!

And then: once I get the above figured out, I'll need to analyze the discount variances within each price level. So for all price level 2 %'s, what is the high/low/average, or maybe even chart them. How would I go about that. Obviously this is very much secondary to the above.

See mini sheet below. Thanks!

Price Level worksheet.xlsx
	A	B	C	D	E
1	Price Level	Item Number	Product Description	Price per Unit	Discount from Level 1
2	Price Level 1	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	73.35
3	Price Level 10	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	55.27	24.65%
4	Price Level 3	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	64.80	11.66%
5	Price Level 4	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	61.10	16.70%
6	Price Level 5	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	59.13	19.39%
7	Price Level 6	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	57.48	21.64%
8	Price Level 7	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	57.18	22.04%
9	Price Level 8	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	56.99	22.30%
10	Price Level 9	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	56.77	22.60%
11	Price Level 2	212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	68.86	6.12%
12		212092	Standard widget, red gasket, alum. head, 3 3/4" exposed	73.35
13	Price Level 1	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	57.75
14	Price Level 10	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	46.20
15	Price Level 3	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	53.49
16	Price Level 4	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	52.15
17	Price Level 5	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	50.85
18	Price Level 6	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	49.58
19	Price Level 7	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	48.34
20	Price Level 8	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	47.62
21	Price Level 9	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	46.90
22	Price Level 2	212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	54.86
23		212312	Light Weight widget, GREEN gasket, alum head, 3 3/4" exposed	57.75
24	Price Level 1	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	62.53
25	Price Level 10	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	50.80
26	Price Level 3	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	58.51
27	Price Level 4	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	57.34
28	Price Level 5	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	56.20
29	Price Level 6	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	55.07
30	Price Level 7	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	53.97
31	Price Level 8	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	52.89
32	Price Level 9	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	51.83
33	Price Level 2	212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	59.40
34		212336	Light Weight Long widget, BLACK gasket, plastic head, 4" exposed	62.53
Sheet1

Cell Formulas
Range	Formula
E3:E11	E3	=(D$2-D3)/D$2

 

[bebo021999]

May be:
E2:

=IF(OR(A2="Price Level 1",A2=""),"",(LOOKUP(2,1/($A$2:A2="Price Level 1")/($B$2:B2=B2),$D$2:D2)-D2)/LOOKUP(2,1/($A$2:A2="Price Level 1")/($B$2:B2=B2),$D$2:D2))

 

[jaugent27]

May be:
E2:

=IF(OR(A2="Price Level 1",A2=""),"",(LOOKUP(2,1/($A$2:A2="Price Level 1")/($B$2:B2=B2),$D$2:D2)-D2)/LOOKUP(2,1/($A$2:A2="Price Level 1")/($B$2:B2=B2),$D$2:D2))

Thank you!!