I have part of a formula already written..need to add another part..

[Marq]

How would I complete this formula...I had help from the baord with the part I have alreay but I want to add another equation into it:

I would like to put in cell M39 to say the following:

If the value in M38 is greater than ZERO then leave M39 blank....BUT if the value of cell M38 has no value then COUNTIF($Z$1:$AC$6,M38-1)+COUNTIF($Z$1:$AC$6,M38+1),0)

 

[repairman615]

Did you try the last posts suggestion in this thread?

http://www.mrexcel.com/forum/showthread.php?t=533903&page=2

 

[repairman615]

Here is another shot after seeing that cell M15 is also in use here.

<TABLE style="WIDTH: 549pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=732><COLGROUP><COL style="WIDTH: 549pt; mso-width-source: userset; mso-width-alt: 13385" width=732><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 549pt; HEIGHT: 30.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=41 rowSpan=2 width=732>=IF(ISNUMBER(M38),"",COUNTIF($Z$1:$AC$6,M15-1)+COUNTIF($Z$1:$AC$6,M15+1))


</TD></TR><!-- / message --><!-- sig --><TR style="HEIGHT: 15.75pt" height=21></TR></TBODY></TABLE>

 

[Marq]

Here is another shot after seeing that cell M15 is also in use here.

<TABLE style="WIDTH: 549pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=732><COLGROUP><COL style="WIDTH: 549pt; mso-width-source: userset; mso-width-alt: 13385" width=732><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 549pt; HEIGHT: 30.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=41 rowSpan=2 width=732>=IF(ISNUMBER(M38),"",COUNTIF($Z$1:$AC$6,M15-1)+COUNTIF($Z$1:$AC$6,M15+1))


</TD></TR><!-- / message --><!-- sig --><TR style="HEIGHT: 15.75pt" height=21></TR></TBODY></TABLE>

still no luck....its ok gang....im beginning to feel awkward now coming back saying it's not working...i'll try to figured out another way to get the results...Thanks again for the all the help.

 

[repairman615]

#NAME?,

This is nice!

=SUM(COUNTIF($Z$1:$AC$6,M15+{1,-1}))

Marq,

I understand what you are saying,

....im beginning to feel awkward

don't think about it. Instead, think how cool it will be when this works as you would like.
I would like to help. Let me know if you still need to sort this out.

Here is my examples:

Excel Workbook
	M	Z	AA	AB	AC
1		27	25	28	30
2		28	27	25	28
3		29	28	24	25
4		30	29	23	24
5		28	30	29	26
6		27	31	27	21
15	27
38
39	6
Sheet1

Excel 2007

Cell Formulas
Range	Formula
M39	=IF(ISNUMBER(M38),"",COUNTIF($Z$1:$AC$6,M15-1)+COUNTIF($Z$1:$AC$6,M15+1))

You can see in the above that while M38 is blank, the formula in M39 evaluates the COUNTIF part (FALSE condition of IF. The COUNTIF part uses cell M15 and in the above, is 27. The COUNTIF is looking for the numbers 28 and 26. There are 6 in the table that are found resulting in 6.


And here is with a 1 in cell M38:

Excel Workbook
	M
38	1
39
Sheet1

Excel 2007

Cell Formulas
Range	Formula
M39	=IF(ISNUMBER(M38),"",COUNTIF($Z$1:$AC$6,M15-1)+COUNTIF($Z$1:$AC$6,M15+1))

Basically, when M38 is blank, the TRUE result of IF is displayed or "".