if range then

[spilner]

if any within in cell C:C=10% then if c21 more than 2000 = c21-100

the formula i use is =IF(C:C=10%,if(C21>2000,C21-"100",)

how do i write this to work?

thanks a lot

 

[repairman615]

Hello,

Try:

<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=430><COLGROUP><COL style="WIDTH: 323pt; mso-width-source: userset; mso-width-alt: 7862" width=430><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 323pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=430>=IF(AND(COUNTIF(C:C,">=30%"),C21>2000),C21-100)


</TD></TR></TBODY></TABLE>

Using Excel 2007

Edit: I see the changes to OP, The above is incorrect.

-Jeff

 

[spilner]

it work but if the range without the 30%.its also count it.

 

[repairman615]

Here is an update to your edited post:

<TABLE style="WIDTH: 317pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=422><COLGROUP><COL style="WIDTH: 317pt; mso-width-source: userset; mso-width-alt: 7716" width=422><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 317pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=422>=IF(AND(COUNTIF(C:C,"=10%")>=1,C21>2000),C21-100,"")

</TD></TR></TBODY></TABLE>

 

[spilner]

it worked man..thanks a lot.i owe u man

 

[repairman615]

Your welcome,

Glad to help!