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)

 

[T. Valko]

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)

Try this...

=IF(M38>0,"",SUM(COUNTIF($Z$1:$AC$6,M38+{1,-1})))

 

[Marq]

Try this...

=IF(M38>0,"",SUM(COUNTIF($Z$1:$AC$6,M38+{1,-1})))

I do not understand the role of the "SUM".....I am not summing up anything..I am "counting"

 

[Marq]

Try this...

=IF(M38>0,"",SUM(COUNTIF($Z$1:$AC$6,M38+{1,-1})))

It does not get me the correct answer...i think the SUM may be the issue maybe??

 

[T. Valko]

It does not get me the correct answer...i think the SUM may be the issue maybe??

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

Does exactly the same thing as

COUNTIF($Z$1:$AC$6,M38-1)+COUNTIF($Z$1:$AC$6,M38+1)

It would also be the same as:

SUM(COUNTIF($Z$1:$AC$6,M38-1),COUNTIF($Z$1:$AC$6,M38+1))

If you're getting an incorrect result then we need a more detailed explanation of what you're wanting to do.

 

[Marq]

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

Does exactly the same thing as

COUNTIF($Z$1:$AC$6,M38-1)+COUNTIF($Z$1:$AC$6,M38+1)

It would also be the same as:

SUM(COUNTIF($Z$1:$AC$6,M38-1),COUNTIF($Z$1:$AC$6,M38+1))

If you're getting an incorrect result then we need a more detailed explanation of what you're wanting to do.

that part is working fine....its the reading of cell M38 if its greater than zero part that isnt working it seems.

What I'm trying to say in the formula in cell M39 is "If cell M38 is greater than ZERO then dont do anything..leave cell M39 blank...BUT if M38 has no value THEN SUM(COUNTIF($Z$1:$AC$6,M38+{1,-1}))

 

[T. Valko]

that part is working fine....its the reading of cell M38 if its greater than zero part that isnt working it seems.

What I'm trying to say in the formula in cell M39 is "If cell M38 is greater than ZERO then dont do anything..leave cell M39 blank...BUT if M38 has no value THEN SUM(COUNTIF($Z$1:$AC$6,M38+{1,-1}))

If M38 has no value then M38 evaluates to 0 and 0+{1,-1} = {1,-1}.

So, if M38 has no value then the formula will COUNT cells in the range that contain 1 and -1.

Is that what you want?

 

[Marq]

If M38 has no value then M38 evaluates to 0 and 0+{1,-1} = {1,-1}.

So, if M38 has no value then the formula will COUNT cells in the range that contain 1 and -1.

Is that what you want?

yes..if M38 has no value the count the number of cells in the range Z1:AC6 that are within 1 (plus 1 or minus 1) of cell M15

 

[T. Valko]

yes..if M38 has no value the count the number of cells in the range Z1:AC6 that are within 1 (plus 1 or minus 1) of cell M15

OK, now you just changed things based on the condition of cell M15!

Try this...

=IF(M38>0,"",IF(M38="",SUM(COUNTIF($Z$1:$AC$6,M15+{1,-1})),""))

I'm assuming that M38 will only contain a number or be an empty/blank cell.

 

[Marq]

OK, now you just changed things based on the condition of cell M15!

Try this...

=IF(M38>0,"",IF(M38="",SUM(COUNTIF($Z$1:$AC$6,M15+{1,-1})),""))

I'm assuming that M38 will only contain a number or be an empty/blank cell.

still not working...could I please send you my example sheet in email?