IF THEN function error

[jmcclain]

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


"jmcclain" <[email protected]> wrote in message
news:[email protected]...
>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
>
>

[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?

In article <[email protected]>,
"jmcclain" <[email protected]> wrote:

> 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

[JE McGimpsey]

Should have been

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

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

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

[ddeering]

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

"jmcclain" wrote:

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

[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

"ddeering" <[email protected]> wrote in message
news:[email protected]...
>I am having the same problem. Have you figured it out?
>
> "jmcclain" wrote:
>
>> 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
>>
>>

[ddeering]

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!

"JulieD" wrote:

> 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
>
> "ddeering" <[email protected]> wrote in message
> news:[email protected]...
> >I am having the same problem. Have you figured it out?
> >
> > "jmcclain" wrote:
> >
> >> 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
> >>
> >>
>
>
>

[ddeering]

H10 also has a formula.

"JulieD" wrote:

> 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
>
> "ddeering" <[email protected]> wrote in message
> news:[email protected]...
> >I am having the same problem. Have you figured it out?
> >
> > "jmcclain" wrote:
> >
> >> 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
> >>
> >>
>
>
>

[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


"ddeering" <[email protected]> wrote in message
news:[email protected]...
>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!
>
> "JulieD" wrote:
>
>> 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
>>
>> "ddeering" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am having the same problem. Have you figured it out?
>> >
>> > "jmcclain" wrote:
>> >
>> >> 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "ddeering" <[email protected]> wrote in message
> news:[email protected]...
>>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!
>>
>> "JulieD" wrote:
>>
>>> 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
>>>
>>> "ddeering" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >I am having the same problem. Have you figured it out?
>>> >
>>> > "jmcclain" wrote:
>>> >
>>> >> 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
>>> >>
>>> >>
>>>
>>>
>>>
>
>

[JulieD]

Hi Trevor

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

Cheers
JulieD



"Trevor Shuttleworth" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>> "ddeering" <[email protected]> wrote in message
>> news:[email protected]...
>>>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!
>>>
>>> "JulieD" wrote:
>>>
>>>> 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
>>>>
>>>> "ddeering" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>> >I am having the same problem. Have you figured it out?
>>>> >
>>>> > "jmcclain" wrote:
>>>> >
>>>> >> 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
>>>> >>
>>>> >>
>>>>
>>>>
>>>>
>>
>>
>
>