Insert a Value based on a Percentage

[dave_br]

Guys,

is it possible to fill in a cell dependant upon the variable in another cell.
i want to be able to offer 4 bands of discounts based on the margin of a product i.e if a product is a 50% margin it may get 30% discount but if it has a 35% margin it may only get 10% discount to still make a profit.

so below is what i am looking to have filled in.

<TABLE style="WIDTH: 196pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=263><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" span=4 width=47><TBODY><TR style="HEIGHT: 40.5pt; mso-height-source: userset" height=54><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 56pt; HEIGHT: 40.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 height=54 width=75>Margin @ 20%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 width=47>Band 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 width=47>Band 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 width=47>Band 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 width=47>Band 4</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 20.1pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=26 width=75>45.58%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>25.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>20.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>10.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>5.00</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 20.1pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=26 width=75>60.04%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>40.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>35.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>25.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>20.00</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 20.1pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=26 width=75>35.33%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>15.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>10.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>2.50</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>0.00</TD></TR></TBODY></TABLE>
the brackets where discounts change are >30 but <35 >35 but <40 etc on the buy in margin.

if i can supply any more info please ask as all help is welcome!

dave

 

[Smoakstack]

Re: Insert a Value based on a Peercentage

set a cell equal to

=if(and(A2:A?>30,A2:A?<35),'insert discount rate here,if(and(A2:A?>35, A2:A?<40),'insert discount rate,"")

See if that works

 

[Smoakstack]

be sure to change the question mark and what your discount rate is

 

[dave_br]

have had a go with that and this formula:
=IF(AND(N2>30,N2<35),10,IF(AND(N2>35,N2<40),15,""))

N2 is the actual cell where the buy in margin is and 10% is the discount for 30-35% buy in and 15% is the discount for 35-40% buy in.
when N2 shows 32.96% cell O2 shows no data but the formula is in it?

looks like it is what im after just needs a few more tweaks!!

dave