Compress Coding

**priceisright** · 06-20-2011, 09:45 AM

I am trying to build a formula that would allow numeric values of cells A2-H2 to affect the following formula, but I keep getting errors if I add too many "values/formulas" to a single string. Is there a better formula to use to build this? I need to be able to attach this formula to numerous spread sheets to modify data. The profit margin has to be different based upon cost of item.

Example:
A2=8% B2=9% C2=10%, etc etc
Cost $25(B5), Margin should be set to 20%(E2), New price is $31.39 (E5)
Cost $125(B5), Margin should be set to 10%(C2), New Price is $139.99 (E5)

*A2-H2 represent gross profit margins
*B5-B1500 represent cost of goods
*E5-E1500 represent new price of goods rounded to the nearest $.99 or $.09
*F5-F1500 represent new price of goods rounded to nearest $.09 when B5-B1500<$10.

Formula for E5:
=IF(B5>=250,SUM(ROUND(B5/(1-$A$2),0),0.99),IF(B5>=175,SUM(ROUND(B5/(1-$B$2),0),0.99),IF(B5>=100,SUM(ROUND(B5/(1-$C$2),0),0.99),IF(B5>=50,SUM(ROUND(B5/(1-$D$2),0),0.99),IF(B5>=25,SUM(ROUND(B5/(1-$E$2),1),0.09),IF(B5>=10,SUM(ROUND(B5/(1-$F$2),1),0.09),""))))))

Formula for F5:
=IF(B5>=4, IF(B5<10, SUM(ROUND(B5/(1-$G$2),1),0.09),""), SUM(ROUND(B5/(1-$H$2),1),0.09))

Excel is not allowing E5 & F5 to be in a single formula due to its limits of number of formulas allows per line.

**Sadath31** · 06-20-2011, 10:30 AM

Formula in E5 can be simplified as below
=ROUND(B5/(1-INDEX({5.6,5.5,5.4,5.3,5.2,5.1},MATCH(B5,{10,25,50,100,175,250}))),0)+0.99

where 5.6 is value in F2
5.5 is value in E2 ... so on

**priceisright** · 06-20-2011, 10:43 AM

The provided formula is now able to be integrated with my spread sheet... error = #N/A

nevermind - I was able to modify it to work. Thank you for the information

**priceisright** · 06-20-2011, 10:51 AM

I'm looking to modify the code a little bit more to be able to adjust A2-H2 within the INDEX function. The index function is not allowing me to reference another cell such as A2 rather than "5.1".

**priceisright** · 06-20-2011, 12:16 PM

I would like to use the referenced compressed formula, but would like to be able to modify the values of A1-H1 (cost brackets) & A2-H2 (profit margin percentages) to be embedded into the formula such as:

=ROUND(A5/(1-INDEX({$H$2,$G$2,$F$2,$E$2,$D$2,$C$2,$B$2,$A$2},MATCH(A5,{$H$1,$G$1,$F$1,$E$1,$D$1,$C$1,$B$1,$A$1}))),1)+0.09

But this causes errors as well. I would like to be able to run as many as 25-30 price brackets in row 1 and price margins in row 2

Please reference following picture for more reference: (columns B & C were calculated using the old formula while E was calculated with the new formula, but will not reference A1-H1 & A2-H2)
\1