Meaning of these formulas

[sxmajmda]

I understand that all these formulas add (except no. 5), but what I really want to know is what do they mean

1) IF(OR(C11<>0,C12),C11+C12,"")


2) IF(OR(ISNUMBER(C10),ISNUMBER(C13)),SUM(C10)+SUM(C13),"")


3) IF(OR(C15<>0,C16),C15+C16,"")


4) IF(SUM(C11:N11),SUM(C11:N11),"")


5) IF(ISNUMBER(C10),C10,"")

Thanks in advance,
SXM

 

[Scott Huish]

They're not all the most efficient for what they do, but here goes:

1. If C11 doesn't equal 0 or C12 is equal to TRUE, then add C11 and C12 (which would coerce to 1 when TRUE is added to C11), else display an empty string (blank)
2. If C10 is a number or C13 is a number then add C10 to C13, else display an empty string (blank)
3. IF C15 doesn't equal 0 or C16 is equal to TRUE, then add C15 to C16 (which would coerce to 1 when TRUE is added to C15), else display an empty string (blank)
4. If the sum of C11:N11 is not zero (which equates to TRUE), then show the sum of C11:N11, else display an empty string (blank)
5. If C10 is a number display C10, else display an empty string (blank)

 

[Andrew Poulsom]

1. If C11 is not equal to zero or C12 is not equal to zero, add C11 and C12, otherwise return null.
2. If C10 is numeric or C13 is numeric, add C10 and C13, otherwise return null.
3. If C15 is not equal to zero or C16 is not equal to zero, add C15 and C16, otherwise return null.
4. If the sum of C11 to N11 is not equal to zero, sum C11 to N11, otherwise return null.
5. If C10 is numeric, return what's in C10, otherwise return null.

Mostly, the purpose of the formulas is to return null instead of zero.

 

[sxmajmda]

thanks a lot

 

[sxmajmda]

I have another quick question ....what does this formula mean...

INDEX(C42:N42,1,COUNT(C42:N42))

 

[Andrew Poulsom]

The COUNT function returns how may numbers there are in the range C2:N42. If that's n, INDEX returns the value of the cell in the nth column of C2:N42.

 

[sxmajmda]

When I get a report out of the system, “column a” should have all the names, instead it is first few rows name is in “column a”, then it is in “column b” then in “column c” and so on….
So I want to add a column before “column a” and in the “new column a” I want to get data from all the other columns…hope it makes sense…

Name			Age
John			30
James			32
Mary			28
Aaron			31
	Bill		26
	Joe			34
	Pete				37
		Ashley			32
		Nolan			26

<tbody>
</tbody>

So once I add the new columns it should appear something like this

Name	Age	Name			Age
John	30	John			30
James	32	James			32
Mary	28	Mary			28
Aaron	31	Aaron			31
Bill	26		Bill		26
Joe	34		Joe			34
Pete	37		Pete				37
Ashley	32			Ashley			32
Nolan	26			Nolan			26

<tbody>
</tbody>

Can anyone help me with this?

 

[Andrew Poulsom]

In A2 copied down:

=INDEX(C2:H2,MATCH(TRUE,INDEX(C2:H2<>"",),FALSE))

and in B2 copied down:

=INDEX(C2:H2,MATCH(9.99999999999999E+307,C2:H2))

 

[sxmajmda]

Thank you so very much, can you please explain the formula..so I can improvise if necessary. Thanks again

 

[Andrew Poulsom]

The first formula returns the first non-blank cell in C2:H2. The second returns the last number in C2:H2. Use the Evaluate Formula Tool to see how.