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

Marq said:

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)

Click to expand...

#NAME? said:

Try this...

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

Click to expand...

#NAME? said:

Try this...

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

Click to expand...

Marq said:

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

Click to expand...

#NAME? said:

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.

Click to expand...

Marq said:

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}))

Click to expand...

#NAME? said:

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?

Click to expand...

Marq said:

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

Click to expand...

#NAME? said:

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.

Click to expand...