formula

[Louise]

I have the following formula
=if($I$33="y",1,1-$Q$37) this is in Q44 cell
I need to add if Q37 is empty use Q40
I can't figure out how to write the formula.
What it does now is if there is a y in cell I33 subtract Q37 from Q44, but
now I need to add use Q37 or Q40

 

[David Biddulph]

=if($I$33="y",1,1-IF($Q$37="",$Q$40,$Q$37))

 

[Louise]

I need the formula to do if Q37 is empty subtract Q40 from Q44. With this
formula I received an error msg, but it seems to be closer to what I need.
Thank you for your assistance so far.

 

[David Biddulph]

I thought you were talking about a formula to go in Q44? You can't subtract
Q40 from Q44 in a formula in Q44 unless you're going to use a circular
reference.

You didn't tell us what error message you got from my formula, so it's
difficult for us to help you.

 

[Louise]

I tried the formula again and did not get an error msg. Probably a typo
before. But Q44 has 100% in it. With your formula if I put 20% in Q40 I get
an answer of
-1900% and I should be getting 80% in Q44. Can that be fixed? At least I
am getting a number instead of true/false.

 

[David Biddulph]

You have replied to your own message. If you are trying to respond to
someone else's message, reply to that one and quote enough of it to put your
reply into context.

If it is my message to which you are trying to reply, you don't seem to have
responded to the point about circular references. Does Q44 have a value in
it, or does it have a formula?

If you were refering to my formula
=if($I$33="y",1,1-IF($Q$37="",$Q$40,$Q$37)), then I don't see where you are
getting -1900%. With 20% in Q40, that formula returns 80% if I33 is not Y
and Q37 is blank. You would, of course, get -1900% if Q40 didn't contain
20% but contained 20 instead. [You will realise that 20 is equivalent to
2000%.]

 

[David Biddulph]

You can avoid typos by using copy and paste. Copy from the group and paste
into the formula bar.

 

[Louise]

The formula you gave me is for Q44. I do not know what circular reference
is. But I do want to subtract Q40 from Q44 if Q37 is empty. Is it possible?

 

[David Biddulph]

Hint:
Type the words "circular reference" into Excel help.

 

[Louise]

David,
I did that and it did not help me much. As you have probably figured out I
have only dealt with simple formulas in the past.
I want to thank you for sharing your vast knowledge of info with me.
I did get the formula to work and the job is complete thanks to your help.
Thank you again.