If And statements -- HELP!
 

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a zero
in the cell but the formula does not read it as a zero. The only way I know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the
way I expect it to.... Is there something I don't understand about formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

Probably something resulting in .000000000001 that you don't see
and(int(d9),

--
Don Guillett
SalesAid Software

"GI JOE" <GI wrote in message
...
I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a zero
in the cell but the formula does not read it as a zero. The only way I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the
way I expect it to.... Is there something I don't understand about formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

Hi Don...

I understand the concept of .000000000001 but there is no reason for these
formulas to return anything but a phrase or a zero... can you see anyway to
remedy this problem?

"Don Guillett" wrote:

Probably something resulting in .000000000001 that you don't see
and(int(d9),

--
Don Guillett
SalesAid Software

"GI JOE" <GI wrote in message
...
I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a zero
in the cell but the formula does not read it as a zero. The only way I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the
way I expect it to.... Is there something I don't understand about formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

I just checked the fx icon next to the formula bar and it indicates the
correct formula outcome so now I'm totally confused!

Why would the outcome not show up in the cell?

"GI JOE" wrote:

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a zero
in the cell but the formula does not read it as a zero. The only way I know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the
way I expect it to.... Is there something I don't understand about formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

I may be wrong but is it not the circular reference that is stopping the
formulas calculating?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
I just checked the fx icon next to the formula bar and it indicates the
correct formula outcome so now I'm totally confused!

Why would the outcome not show up in the cell?

"GI JOE" wrote:

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a
zero
in the cell but the formula does not read it as a zero. The only way I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts
the
way I expect it to.... Is there something I don't understand about
formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

Good morning Sandy (Toronto Time)

Thank you for your help... Is there a way to turn off the circular reference
in order for the value to appear?

Joe.

"Sandy Mann" wrote:

I may be wrong but is it not the circular reference that is stopping the
formulas calculating?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
I just checked the fx icon next to the formula bar and it indicates the
correct formula outcome so now I'm totally confused!

Why would the outcome not show up in the cell?

"GI JOE" wrote:

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a
zero
in the cell but the formula does not read it as a zero. The only way I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts
the
way I expect it to.... Is there something I don't understand about
formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

Hi GI JOE,

No there is no way that I know of to turning off the circular reference.
You can turn Iteration on or off but that does not stop the circular
references and your

Perhaps if you explain what it is that you are trying to do some of the
clever people around here will be able to give you an alternarive or work
around.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
Good morning Sandy (Toronto Time)

Thank you for your help... Is there a way to turn off the circular
reference
in order for the value to appear?

Joe.

"Sandy Mann" wrote:

I may be wrong but is it not the circular reference that is stopping the
formulas calculating?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
I just checked the fx icon next to the formula bar and it indicates the
correct formula outcome so now I'm totally confused!

Why would the outcome not show up in the cell?

"GI JOE" wrote:

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a
zero
in the cell but the formula does not read it as a zero. The only way
I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts
the
way I expect it to.... Is there something I don't understand about
formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

Hello again Sandy...

Thank you for reminding me that you cannot shut off circular reference
errors and so I came up with an alternative string in an adjacent cell to
avoid the error... and well I seems to have solved the problem!

Thank you to all who worked on this with (and for) me!

This is the first time I used a discussion group to resolve anything and I
must say it is an excellent forum!

Joe.

"Sandy Mann" wrote:

Hi GI JOE,

No there is no way that I know of to turning off the circular reference.
You can turn Iteration on or off but that does not stop the circular
references and your

Perhaps if you explain what it is that you are trying to do some of the
clever people around here will be able to give you an alternarive or work
around.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
Good morning Sandy (Toronto Time)

Thank you for your help... Is there a way to turn off the circular
reference
in order for the value to appear?

Joe.

"Sandy Mann" wrote:

I may be wrong but is it not the circular reference that is stopping the
formulas calculating?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
I just checked the fx icon next to the formula bar and it indicates the
correct formula outcome so now I'm totally confused!

Why would the outcome not show up in the cell?

"GI JOE" wrote:

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see a
zero
in the cell but the formula does not read it as a zero. The only way
I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts
the
way I expect it to.... Is there something I don't understand about
formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0

If And statements -- HELP!
 

Hi Joe,

Glad you got it sorted.

This is the first time I used a discussion group to resolve anything and I
must say it is an excellent forum!

Yes I agree this is an excellent NG with everyone dedicated to helping each
other. The level of knowledge of Excel astonishes me bu the 'mind reading'
abilities of some of the contributors leaves me speechless.


--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
Hello again Sandy...

Thank you for reminding me that you cannot shut off circular reference
errors and so I came up with an alternative string in an adjacent cell to
avoid the error... and well I seems to have solved the problem!

Thank you to all who worked on this with (and for) me!

This is the first time I used a discussion group to resolve anything and I
must say it is an excellent forum!

Joe.

"Sandy Mann" wrote:

Hi GI JOE,

No there is no way that I know of to turning off the circular reference.
You can turn Iteration on or off but that does not stop the circular
references and your

Perhaps if you explain what it is that you are trying to do some of the
clever people around here will be able to give you an alternarive or work
around.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
Good morning Sandy (Toronto Time)

Thank you for your help... Is there a way to turn off the circular
reference
in order for the value to appear?

Joe.

"Sandy Mann" wrote:

I may be wrong but is it not the circular reference that is stopping
the
formulas calculating?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"GI JOE" wrote in message
...
I just checked the fx icon next to the formula bar and it indicates
the
correct formula outcome so now I'm totally confused!

Why would the outcome not show up in the cell?

"GI JOE" wrote:

I don't understand why this formula does not work

Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) )
Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) ))
Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) ))

If I check off display zero values in my options menu then I do see
a
zero
in the cell but the formula does not read it as a zero. The only
way
I
know
it doesn't is when I type in a zero in cell D 12 then cell D 11
reacts
the
way I expect it to.... Is there something I don't understand about
formula
writing or is this one of those so called glitches?

This is what it looks like when the formula is working on its own
Total Kms for the Day 76


___________________________________________
This is what it looks like when I type in a zero in cell D 12
Total Kms for the Day 76
Total Kms to date 76
0