How to require a field in a calculation (excel 2003) 2

[splats]

Hello

We are using an if... then statement, however, it brings back a circular reference. Anyone know how to require a field in a calculation?

I want field h9 to be required and the value 0 or less is not allowed. Therefore, I want field i9 to be calculated with the value of the field h9. as so, this is my wish...

i9=((G8*5500) -(0.08 *G8*5500)-(G8*H8))/1000000
If h9 = 0, then i want i9 to be equal to 0 instead.

Thank you

Tinatab

 

[straybullet]

=IF(H9>0,((G8*5500) -(0.08 *G8*5500)-(G8*H8))/1000000,0) will do the calculation

It looks at the value of H9, and if it is greater than 0, performs the desired calculation.

Is it possible to make H9 a calculated cell? If not, you could set it up to be 0 by default. Either way, even if left blank, the calculation works.

Let them hate - so long as they fear... Lucius Accius

 

[splats]

Hello

Thank you for your response, Unfortunately, this did not solve our problem as we still do get the calculation working when the h9 field is left blank. Therefore, we would like the calculation to not work when h9 is 0 or blank. Any ideas?

thank you

tinatab

 

[straybullet]

I'm not quite sure if this is what you mean, but you could format I9 to not display 0 value, so that it appears to be blank. If H9 is blank, equal to 0, or less than 0, the above formula will put 0 in I9.

i9=((G8*5500) -(0.08 *G8*5500)-(G8*H8))/1000000
If h9 = 0, then i want i9 to be equal to 0 instead.

Let them hate - so long as they fear... Lucius Accius

 

[mintjulep]

Straybullet's solution should work if H9 is BLANK.

It will return the calculation if H9 contains one or more spaces (which LOOK the same as a BLANK cell, but are not the same).

 

[straybullet]

Actually, it does work... at least in my copy of Excel. Deleting the value in H9 results in a 0 in I9, as it should.

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

mint, just realized that my last post could be taken wrong - I wasn't implying anything towards you of course.. rather adding on (to tinat) that other things may need checking...

Let them hate - so long as they fear... Lucius Accius

 

[mintjulep]

No worries.

 

[splats]

thank you all I have it working now. y'all rock!

tinatab