IF THEN function error

[Guest]

I am trying to write an IF THEN argument such as..

=IF((B11-(C11:H11))=0,"YES","NO"

Where a value of YES will be returned if the condition is = 0 and a value of
NO will be returned if the condition is not equal to 0.

Where B11 to G11 are input values and H11 is the result of a formula
calculation.

I keep getting a #Value error...

Can anyone suggest a fix?

Jon

 

[Trevor Shuttleworth]

Jon

maybe something like:

=IF((B11-SUM(C11:H11))=0,"YES","NO")

Regards

Trevor

 

[JE McGimpsey]

Are you trying to subtract the SUM of C11:H11 from B11?

=IF(B11-SUM(C11:H11)=0,"YES","NO")

or, equivalently:

=IF(B11=SUM(C11:H11),"YES","NO")

If not, what are you trying to do?

 

[JE McGimpsey]

Should have been

=IF((B11-SUM(C11:H11))=0,"YES","NO")

 

[Guest]

I am having the same problem. Have you figured it out?

 

[JulieD]

Hi

if you'ld like to type out a sample of your data and include your current
formula i'll be happy to look at it as John McGimpsey's answer to jmcclain
seems like it should work.

Cheers
JulieD

 

[Guest]

I have tried the solution offered in previous response to no avail. Perhaps
there are additional problems with my info, however, it's seems rather silly
that I'm having such a problem. Here is what I have:

J10 contains: =IF(L10=0,"",J9-K9)
L10 contains: =IF(G10=0,"",G10+H10+J10)
G10 contains: =IF(E10=0,"",G9-F10)

I hope you can help! Thanks!

 

[Guest]

H10 also has a formula.

Hi

if you'ld like to type out a sample of your data and include your current
formula i'll be happy to look at it as John McGimpsey's answer to jmcclain
seems like it should work.

Cheers
JulieD

 

[JulieD]

Hi

firstly, "" in a cell, if used in another formula will return a #VALUE
error, so my advice would be to change this to 0, additionally,
=IF(G10=0,0,G10+H10+J10)
should be changed to
=IF(G10=0,0,H10+J10)
as if G10 = 0 then you would have 0+H10+J10

however, neither of these will solve the problem which is you have L10
calculatating J10, which itself is based on L10 - ie a circular
relationship - this is possible by changing the number of iterations to 1
under tools / options / calculation - and checking the interation box - but
i would suggest that you explore ways of restructuring your worksheet first,
if at all possible.

Cheers
JulieD

 

[Trevor Shuttleworth]

Julie

=IF(G10=0,0,G10+H10+J10)
should be changed to
=IF(G10=0,0,H10+J10)
as if G10 = 0 then you would have 0+H10+J10

would you run that by me again ? The bit about when G10 = 0. Not with
that.

G10 is 0 => return 0
G10 is not 0 => return G10+H10+J10

Regards

Trevor

 

[JulieD]

Hi Trevor

sorry ... you're right of course ...

Cheers
JulieD