countif help

[tm1974]

is there such a way to write a formula to count the number that are within 10% of a particular number or a number in a cell?

thanks in advance

 

[Mindpsyche]

is there such a way to write a formula to count the number that are within 10% of a particular number or a number in a cell?

thanks in advance

Try This

Excel 2007
	B	C	D	E	F	G	H
2	Number List	Number Threshold	10 % of Number Threshold	Upper limit	Lower Limit	Count or Not	Countif
3	20	20	2	22	18	Yes	5
4	22					Yes
5	23
6	28
7	18					Yes
8	16
9	20					Yes
10	21					Yes
11	25
Sheet1

Cell Formulas
Range	Formula
D3	=C3/100*10
E3	=C3+D3
F3	=C3-D3
G3	=IF(AND(B3<=$E$3,B3>=$F$3),"Yes","")
G4	=IF(AND(B4<=$E$3,B4>=$F$3),"Yes","")
G5	=IF(AND(B5<=$E$3,B5>=$F$3),"Yes","")
G6	=IF(AND(B6<=$E$3,B6>=$F$3),"Yes","")
G7	=IF(AND(B7<=$E$3,B7>=$F$3),"Yes","")
G8	=IF(AND(B8<=$E$3,B8>=$F$3),"Yes","")
G9	=IF(AND(B9<=$E$3,B9>=$F$3),"Yes","")
G10	=IF(AND(B10<=$E$3,B10>=$F$3),"Yes","")
G11	=IF(AND(B11<=$E$3,B11>=$F$3),"Yes","")
H3	=COUNTIF(G3:G11,"Yes")

Hopefully it does what you need.

 

[Robert Mika]

Try:
Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>*</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">5.6</td><td style="text-align:right; ">6</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">4</td><td>*</td><td>*</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">5.8</td><td>*</td><td>*</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">20</td><td>*</td><td>*</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=SUMPRODUCT(--($A$2:$A$5 <=1.1*B2),--($A$2:$A$5 > =B2*0.9))<b2)< span=""></b2)<></td></tr></tbody></table></td></tr></tbody></table>

To correctly show the formula on this forum had to add spaces before and afte ">","<".
Remove them when copying.

Excel tables to the web >> Excel Jeanie HTML 4

 

[circledchicken]

Hi,

Another alternative might be:

Excel Workbook
	A	B	C	D	E
1	List of numbers		Lookup Value	Count of numbers within 10% of lookup value	Excel 2007+
2	5		10	3	3
3	11
4	12
5	10
6	9
Sheet1

Excel 2010

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(A2:A6<=1.1*C2),--(A2:A6>=0.9*C2))
E2	=COUNTIFS(A2:A6,"<="&1.1*C2,A2:A6,">="&0.9*C2)

 

[Mindpsyche]

Hey guys,

Sorry to hijack the thread but could you explain how SUMPRODUCT works?

Cool responses btw.

 

[circledchicken]

Hey guys,

Sorry to hijack the thread but could you explain how SUMPRODUCT works?

Cool responses btw.

Hi Mindpsyche,

Here are some links that might help:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
http://www.youtube.com/watch?v=RGwPqTG2BnM
http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

 

[Mindpsyche]

Hi Mindpsyche,

Here are some links that might help:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
http://www.youtube.com/watch?v=RGwPqTG2BnM
http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

Thanks

 

[T. Valko]

Hey guys,

Sorry to hijack the thread but could you explain how SUMPRODUCT works?

Cool responses btw.

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[tm1974]

Hi,

Another alternative might be:

Excel Workbook
	A	B	C	D	E
1	List of numbers		Lookup Value	Count of numbers within 10% of lookup value	Excel 2007+
2	5		10	3	3
3	11
4	12
5	10
6	9
Sheet1

Excel 2010

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(A2:A6<=1.1*C2),--(A2:A6>=0.9*C2))
E2	=COUNTIFS(A2:A6,"<="&1.1*C2,A2:A6,">="&0.9*C2)

Thanks for all your responses - worked like a charm