Hi All -
I currently have a very long and complicated formula that i am positive can be made more simple but just not sure exactly how to accomplish it. I have a table of products, max price, min price, and average as shown below in one tab of an excel
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Product[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]productA[/TD]
[TD]$2.00[/TD]
[TD]$1.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]productB[/TD]
[TD]$5.00[/TD]
[TD]$1.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]ProductC[/TD]
[TD]$10.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
In another tab I have products and prices listed that may fall within the range or without. If it falls within the range for that specific product then i want to use the price shown. If it falls outside of the range then i want to use the average.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnF[/TD]
[TD]ColumnG[/TD]
[TD]ColumnH[/TD]
[/TR]
[TR]
[TD]Row6[/TD]
[TD]Product [/TD]
[TD]Price[/TD]
[TD]Price New (adjusted based on above) - this is where i need to adjust my formula[/TD]
[/TR]
[TR]
[TD]Row7[/TD]
[TD]productA[/TD]
[TD]$1.05[/TD]
[TD]$1.05[/TD]
[/TR]
[TR]
[TD]Row8[/TD]
[TD]productA[/TD]
[TD]$3.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row9[/TD]
[TD]productB[/TD]
[TD]$2.75[/TD]
[TD]$2.75[/TD]
[/TR]
[TR]
[TD]Row10[/TD]
[TD]productC[/TD]
[TD]$5.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
Can someone help me with this or point me in the right direction of what type of formula i shoud use?
THanks so much!
****** id="cke_pastebin" style="position: absolute; top: -3.8147e-06px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Product[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]productA[/TD]
[TD]$2.00[/TD]
[TD]$1.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]productB[/TD]
[TD]$5.00[/TD]
[TD]$1.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]ProductC[/TD]
[TD]$10.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
</body>
I currently have a very long and complicated formula that i am positive can be made more simple but just not sure exactly how to accomplish it. I have a table of products, max price, min price, and average as shown below in one tab of an excel
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Product[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]productA[/TD]
[TD]$2.00[/TD]
[TD]$1.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]productB[/TD]
[TD]$5.00[/TD]
[TD]$1.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]ProductC[/TD]
[TD]$10.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
In another tab I have products and prices listed that may fall within the range or without. If it falls within the range for that specific product then i want to use the price shown. If it falls outside of the range then i want to use the average.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnF[/TD]
[TD]ColumnG[/TD]
[TD]ColumnH[/TD]
[/TR]
[TR]
[TD]Row6[/TD]
[TD]Product [/TD]
[TD]Price[/TD]
[TD]Price New (adjusted based on above) - this is where i need to adjust my formula[/TD]
[/TR]
[TR]
[TD]Row7[/TD]
[TD]productA[/TD]
[TD]$1.05[/TD]
[TD]$1.05[/TD]
[/TR]
[TR]
[TD]Row8[/TD]
[TD]productA[/TD]
[TD]$3.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row9[/TD]
[TD]productB[/TD]
[TD]$2.75[/TD]
[TD]$2.75[/TD]
[/TR]
[TR]
[TD]Row10[/TD]
[TD]productC[/TD]
[TD]$5.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
Can someone help me with this or point me in the right direction of what type of formula i shoud use?
THanks so much!
****** id="cke_pastebin" style="position: absolute; top: -3.8147e-06px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Product[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]productA[/TD]
[TD]$2.00[/TD]
[TD]$1.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]productB[/TD]
[TD]$5.00[/TD]
[TD]$1.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]ProductC[/TD]
[TD]$10.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
</body>