Using "IF" in a Formula

[Corey Brock]

If I enter the word "bounced" in one cell can I trigger a
value in another cell ($500.00) to become a negative
number (-$500.00)?

Regrads,
Corey Brock

 

[Ken Wright]

=IF(A1="bounced",-500,500)

or if the 500 is from another cell

=IF(A1="bounced",-H5,H5)

The formula has to be in the cell that is to change.

 

[Guest]

What if there is already a formula in that cell that needs
to be changed. In this case G39 is "=SUM(G25:G38" and I
need D25 though D28 to have the option for the "bounced
text.
For Example: If D25 is "bounced", then the value in G25
should become a negative number which will make the
adjustment on the total for G39

Corey

 

[Ken Wright]

Given the formula in G39 it will automatically pick up any negative anyway,
so what you need to do is cater for whatever is in cells G25:G38 and have
them change if their opposites in Col D have that text. So what do you have
in G25:G38?

 

[Corey Brock]

G25 through G38 are payments received by clients. I need
to be able to leave that payment as it was originaly
entered as a record. If My accountant sees that that
payment, days later, bounces, I want him to be able to
enter "bounced" in Col. D for the appropriate row.
So "bounced" would be entered in D25 if G25 ($500)
bounced, which I then need to be reflected in the total
showing in G39. This IF "BOUNCED" needs to repeat itself
for each row 25-38 to give me a real total on G39 for all
payements in. Also, formulas in other cells reference the
number in G39...

Corey

 

[RagDyer]

Do I understand you to say that G25:G38 contain keyed in data only?
AND, you want the data to remain there?

If that's the case, then let the formula in G39 do the decision making, as
to whether or not to include the individual cells into the total.

Try either of these in G39:

=SUMIF(D2538,"<>bounced",G25:G38)
OR
=SUMPRODUCT((D2538<>"bounced")*G25:G38)

You could get real fancy and also try conditional formatting in ColumnG, so
that when "bounced" appears in ColumnD, the corresponding cell in ColumnG
could turn Red, allowing you to easily keep track of exactly which numbers
are not included in the sum.

Select G25:G38, then:
<Format> <ConditionalFormat>
Change "Cell Value Is" to "Formula Is",
And enter this formula:
=D25="bounced"

Then click on "Format", and choose RED and Bold,
Then <OK> <OK>.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

G25 through G38 are payments received by clients. I need
to be able to leave that payment as it was originaly
entered as a record. If My accountant sees that that
payment, days later, bounces, I want him to be able to
enter "bounced" in Col. D for the appropriate row.
So "bounced" would be entered in D25 if G25 ($500)
bounced, which I then need to be reflected in the total
showing in G39. This IF "BOUNCED" needs to repeat itself
for each row 25-38 to give me a real total on G39 for all
payements in. Also, formulas in other cells reference the
number in G39...

Corey