#### Using "IF" in a Formula

```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)?

Corey Brock

```
 0
anonymous (74722)
1/11/2005 11:49:39 PM
excel.misc 78881 articles. 5 followers.

5 Replies
621 Views

Similar Articles

[PageSpeed] 59

```=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.

--
Regards
Ken.......................    Microsoft MVP - Excel
Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Corey Brock" <anonymous@discussions.microsoft.com> wrote in message
news:210501c4f838\$36cb0bb0\$a301280a@phx.gbl...
> 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)?
>
> Corey Brock
>

```
 0
ken.wright (2489)
1/12/2005 12:16:18 AM
```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

>-----Original Message-----
>=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.
>
>--
>Regards
>           Ken.......................    Microsoft MVP -
Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
>----------------------------------------------------------
------------------
>It's easier to beg forgiveness than ask permission :-)
>----------------------------------------------------------
------------------
>
>"Corey Brock" <anonymous@discussions.microsoft.com> wrote
in message
>news:210501c4f838\$36cb0bb0\$a301280a@phx.gbl...
>> 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)?
>>
>> Corey Brock
>>
>
>
>.
>
```
 0
anonymous (74722)
1/12/2005 12:27:00 AM
```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?

--
Regards
Ken.......................    Microsoft MVP - Excel
Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<anonymous@discussions.microsoft.com> wrote in message
news:211e01c4f83d\$6ebed470\$a301280a@phx.gbl...
> 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
>
> >-----Original Message-----
> >=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.
> >
> >--
> >Regards
> >           Ken.......................    Microsoft MVP -
> Excel
> >              Sys Spec - Win XP Pro /  XL 97/00/02/03
> >
> >----------------------------------------------------------
> ------------------
> >It's easier to beg forgiveness than ask permission :-)
> >----------------------------------------------------------
> ------------------
> >
> >"Corey Brock" <anonymous@discussions.microsoft.com> wrote
> in message
> >news:210501c4f838\$36cb0bb0\$a301280a@phx.gbl...
> >> 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)?
> >>
> >> Corey Brock
> >>
> >
> >
> >.
> >

```
 0
ken.wright (2489)
1/12/2005 12:37:21 AM
```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

>-----Original Message-----
>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?
>
>--
>Regards
>           Ken.......................    Microsoft MVP -
Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
>----------------------------------------------------------
------------------
>It's easier to beg forgiveness than ask permission :-)
>----------------------------------------------------------
------------------
>
><anonymous@discussions.microsoft.com> wrote in message
>news:211e01c4f83d\$6ebed470\$a301280a@phx.gbl...
>> 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
>>
>> >-----Original Message-----
>> >=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.
>> >
>> >--
>> >Regards
>> >           Ken.......................    Microsoft
MVP -
>> Excel
>> >              Sys Spec - Win XP Pro /  XL 97/00/02/03
>> >
>> >-------------------------------------------------------
---
>> ------------------
>> >It's easier to beg forgiveness than ask permission :-)
>> >-------------------------------------------------------
---
>> ------------------
>> >
>> >"Corey Brock" <anonymous@discussions.microsoft.com>
wrote
>> in message
>> >news:210501c4f838\$36cb0bb0\$a301280a@phx.gbl...
>> >> 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)?
>> >>
>> >> Corey Brock
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>
```
 0
anonymous (74722)
1/12/2005 12:57:35 AM
```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(D25:D38,"<>bounced",G25:G38)
OR
=SUMPRODUCT((D25:D38<>"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!
==============================================

"Corey Brock" <anonymous@discussions.microsoft.com> wrote in message
news:1d4f01c4f841\$b46f1d00\$a501280a@phx.gbl...
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

>-----Original Message-----
>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?
>
>--
>Regards
>           Ken.......................    Microsoft MVP -
Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
>----------------------------------------------------------
------------------
>It's easier to beg forgiveness than ask permission :-)
>----------------------------------------------------------
------------------
>
><anonymous@discussions.microsoft.com> wrote in message
>news:211e01c4f83d\$6ebed470\$a301280a@phx.gbl...
>> 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
>>
>> >-----Original Message-----
>> >=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.
>> >
>> >--
>> >Regards
>> >           Ken.......................    Microsoft
MVP -
>> Excel
>> >              Sys Spec - Win XP Pro /  XL 97/00/02/03
>> >
>> >-------------------------------------------------------
---
>> ------------------
>> >It's easier to beg forgiveness than ask permission :-)
>> >-------------------------------------------------------
---
>> ------------------
>> >
>> >"Corey Brock" <anonymous@discussions.microsoft.com>
wrote
>> in message
>> >news:210501c4f838\$36cb0bb0\$a301280a@phx.gbl...
>> >> 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)?
>> >>
>> >> Corey Brock
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

```
 0
ragdyer1 (4060)
1/12/2005 1:49:58 AM

Similar Artilces:

i need to write an IF function formula, how?
i need to write an IF function that displays "made" if the average in H5 is less than 76 and "cut" if the score is not less than 76. help?!! =if(h5<76,"made","cut") maxwell wrote: > > i need to write an IF function that displays "made" if the average in H5 is > less than 76 and "cut" if the score is not less than 76. help?!! -- Dave Peterson ...

using macro within an if function
Can you put macros into formulas to run automatically if special criteria is met? Hi Ben! Formulas only return values to a cell. But you can use a Worksheet_change event handling macro that will run based upon the results of a target cell. A Google search on Worksheet_change should produce lots of examples that might be tailored to your requirements. Or you could post more details of what you intend. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Ben&...

Function or formula to add different formatted cells
Is there a way in which I can use a function or formula to add, multipl and/or divide cells which are formatted differently and get an en result? I am specifically needing to divide a general number by tim frames (hh:mm:ss) and multiplying by another general number, yet I hav no idea if that can be done. Help -- rcdunn200 ----------------------------------------------------------------------- rcdunn2003's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=138 View this thread: http://www.excelforum.com/showthread.php?threadid=26894 Hi as a starting point: http://ww...

Using formulas in conditions (SUMIF, AVERAGEIF)
Hi, I want to use formula in condition syntax, but I don't know how to reference the value being tested. Like: SUMIF(A1:A10;">10 AND <20") Or: AVERAGEIF(A1:A10;"ISODD(...)") Thanks for any ideas. Jan For your <<< SUMIF(A1:A10;">10 AND <20") >>> Try: =Sumif(A1:A10,">10")-Sumif(A1:A10,">=20) OR =SUMPRODUCT((A1:A10>10)*(A1:A10<20)*A1:A10) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGro...

How to make a formula display results only if value is greater tha
I am new to Excel formulas. I assume i need an IF statement but not sure on correct syntax =D2-B2 is what i have now, what can i add to it so that it only displays values >0? Hi =IF(D2-B2>0,D2-B2,"") "superfooz74" wrote: > I am new to Excel formulas. I assume i need an IF statement but not sure on > correct syntax > =D2-B2 is what i have now, what can i add to it so that it only displays > values >0? =if(d2-b2>0,d2-b2,"") "superfooz74" <superfooz74@discussions.microsoft.com> wrote in message news:C57B3ED5-1197-4056...

WEEKDAY using IF
I have a cell(C27) with the foumula: =TODAY()+1 When I open the file on Friday, it shows Saturday. I want it to show Monday. How do I modify the formula if C21 is Saturday(6), then Weekday +2 My formula that doesn't work is: =IF(TODAY()+1=WEEKDAY(6),TODAY()+3) Am I close??? Joe One way: =IF(WEEKDAY(TODAY())>5,TODAY()-WEEKDAY(TODAY(),3)+7,TODAY()+1) another, if you have the Analysis Toolpak Add-in loaded (Tools/Addins..): =WORKDAY(TODAY(),1) In article <uqXKY5g2EHA.3128@TK2MSFTNGP14.phx.gbl>, "lunker55" <this_is_not_my_email_address@hotmail.com> w...

Why do OnLButtonDown and OnMouseWheel use different coordinates?
I raised a WM_LBUTTONDOWN AND WM_MOUSEWHEEL at the same position of a window, but found that the CPoint pt in the OnMouseWheel is different from the CPoint point. I tried many times, the CPoint point is alway the same at a certain position but the CPoint pt varies in a range of 100, why are these two coordinates different? And how can I transform them? "wang" <wang_jia_ji@21cn.com> wrote in message news:087701c38559\$d97828a0\$a301280a@phx.gbl... > I raised a WM_LBUTTONDOWN AND WM_MOUSEWHEEL at the same > position of a window, but found that the CPoint pt in the >...

Using DateDiff
I know I can Google this, and have, but am up against a deadline, and I'm not sure how to "convert" text dates such that they can be used with DateDiff. I have two dates, output by a batch file, in the format: Time1 = "Fri 01/22/2010 16:18:17.37" Time2 = "Sun 01/24/2010 04:32:11.33" I want to calculate the difference between these, with something like: TimeElapsed = DateDiff("h", Time1, Time2) and I need the time thrown in there, not just considering dates. I think I have to convert Time1 and Time2 to some other type of value first...

Is it possible to use multiple IF and OR
SPS looks like ColA ColB ColC ColD ColE ColF N N N N N N N N N Y Y N N N N I need to create a new column that says if there is only N's OR blanks in any row from Col A-F, put a Y, so in the example above ColG would have a Y in rows 1 and 4. Is this doable with IF/OR/AND? Thanks Lynn Hi, Lynn- You could count the Ns and blanks; the opposite way to think of that same scenario is the absence of Ys. If you like that logic, yo...

Need to scan xls files and identify if they use macros...wanna use VB
I was given a project to scan all of the Excel files on our network and generate a report listing the files and wether they use Macros. I was thinking of using VB6 to scann all dirs/subdirs...easy part. Question: -- How do I quickly scan an excel file and determine if it has/uses Macros using VB6. I appreciate you taking the time to read this and any help anybody can provide. Thanks. I was on a project to do something similar, and besides an automated chaeck, there was a need to have each Excel file with potential code/upgrade issues checked. I believe there is a HasMacro bit you can ...

CreateEx used with manifest...
Hello, I am using a manifest file to emulate the windows xp style, but I am having a problem with edit box controls that I create dynamically - I am trying to give them the same look as edit box controls manually put on the dialog, but the dynamic edit boxes look incorrect. Here is what I am doing to create the edit box: bRetVal = pEdit->CreateEx( WS_EX_CLIENTEDGE, "EDIT", "", WS_CHILD | WS_VISIBLE | WS_TABSTOP | WS_BORDER | ES_AUTOHSCROLL, rect, this, m_iControlID); How can I get the vi...

Using IF Function
If the number in B1 is greater than the number in A1 and the word to appear is INVERTED then =IF(B1>A1,"INVERT","NORMAL") works in C1. How can I format cell C1 so that it is red for INVERT. Take a look at Format|conditional formatting. You can change the format based on the value in the cell. Old Red One wrote: > > If the number in B1 is greater than the number in A1 and the word to appear > is INVERTED then =IF(B1>A1,"INVERT","NORMAL") works in C1. How can I format > cell C1 so that it is red for INVERT. -- Dave Peterson ...

Increase value of number in formula/using named cells. And use of IF sentence
Hello. I am new to this group and will try to explain two problems. Hope you understand my poor english. My questions are probably easy for most of you, so I hope someone will help :-) Problem one. I am making a spreadsheet where I have to use names not cell numbers (can not use B1, C3 and so on). I shall use the f(X)=ax3 + bx2 + cx + d formula (the number 3 and 2 are exponents -if that is what is it called). I am going to write a start value (lets name the cell Start), a step value (Step) and a,b,c and d. The last four cells are named a, b, c_ and d. All this is quite all right but i a...

How to use If Then
I have a form field dropdown box that has two options. I need to put an If statement after it that should read something like "If Box=Applies "more text" else blank." So the following text will either appear or remain blank. I can't figure out how to reference a field form entry in the If function or if this is even possible. Please help. Hi That70sHeidi, Your IF field's construction should be: {IF{REF Dropdown1}= "Applies" "Additional text"} To get the correct 'name' to use for 'Dropdown1', right-click on the for...

using if in a range
I am trying to workout numerology in excel: I have number 1-9 in row - 1 Alphabets A-I in row - 2 Alphabets J-R in row - 3 Alphabets S-Z in row - 4 I wish to know if I input a value from "A-Z" in different cells, I should get corresponding numeric values say A20=S,B20=A,C20=I in three different cells should return A21=1,B21=1,C21=9 Can somebody help me resolve this? Thanks, =MOD(CODE(A20)-65,9)+1 Or, if the numbers in row 1 might change: =INDEX(\$A\$1:\$A\$20,MOD(CODE(A\$20)-65,9)+1) "Sai" <Sai@discussions.microsoft.com> wrote in message...

using the IIF
i am having trouble with getting the statement to work like i need: IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request","Second Request ") AS Expr1 What I need to do is nest this statement so that the result will show Counter = 0, blank Counter= 1, “First Request” Counter = 2, “Second Request” Etc…. As of now, it returns "Second Request for all records. On Fri, 21 Mar 2008 10:09:01 -0700, ACSer wrote: > i am having trouble with getting the statement to work like i need: > > IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request",&qu...

=IF(ISERROR('Tank Sizes'!\$F\$30-('Tank Sizes'!\$F\$30/B3))/3.79,"",('Tank Sizes'!\$F\$30-('Tank Sizes'!\$F\$30/B3))/3.79) How do I add =If(B3<D3,"" to this formula I don't want the results to show up if it's a negative number. Thanks in Advance! As well as adding that snippet plus a comma to the beginning, you will also have to add a closing bracket to the end, like this: =3DIF(B3<D3,"",IF(ISERROR('Tank Sizes'!\$F\$30-('Tank Sizes'!\$F\$30/B3))/ 3.79,"",('Tank Sizes'!\$F\$30-('Tank Sizes'...

Using COUNTIF (specific cells over multiple sheets) � can anyone help?
Hi I�d be really grateful if someone could assist me in how to �formula the below: I have twelve identically formatted worksheets (�A� through to �J�) In each of the cells B6:F6, B10:F10, B14:F14, B18:F18 & B22:F22 on eac sheet there will be a different one of 5 words (each word can and wil occur more than once within the above range). For the purpose of thi exercise I need to ignore the text in the other rows (eg 7-9, 11-1 etc). What I need to tally on a separate sheet within the workbook is ho many times each of the five words occur in the group above (eg B6:F6 B10:F10, B14:F14, B...

Adjusting formula so it works even if some rows above empty?
I have a dollar amount in cell F23, say, with a formula in G23 to deduct that from subtotal in G22. The formula in G23 looks like this: =IF(\$F23<>"",(\$G22-\$F23),"") This works great. But I've run into a problem I need to adjust for and can't figure out. These columns, F and G, from rows 18 to 31 are for miscellaneous information. Sometimes I have amounts to deduct, other times it's just info in the description column B to the left of F (there are some merged cells there) about payments in the rest of the spreadsheet and no amount needs go into column 2...

Using excel if function on a pricing spreadsheet
I am trying to simplify a pricing spreadsheet that I use at work. I a using the following criteria; the \$ value of a product, the descriptio of a product that I have on a cost page. What I want to do is when type in the description of product X in a specific cell, I want the value of that product returned to another specific cell. Example: Lexan14SS (in a cell); 1.75 (\$ value in another cell). On a pricing spreadsheet I type in Lexan14SS and I want that specific value returned to another cell. Of course though i will have mutipl product descriptions and prices to go along with those produc...

Specifying printer using Open For Output method?
Right now I have Open "LPT1:" For Output As #1 But I can't use my LPT1 printer at the moment. Is there a way I can specify a networked printer? Ed Not sure what this has to do with Excel spread sheets, but you can do "NET USE LPT2 \\computername\sharedprinter\" and then you should be able to "Open "LPT2:............... Ed wrote: > Right now I have > Open "LPT1:" For Output As #1 > But I can't use my LPT1 printer at the moment. Is there a way I can > specify a networked printer? > > Ed > On May 3, 7:42 am, Bo...

Countif using less than or greater than criteria
I have a list of data in cells d11:d15. I want to be able to count how many of the data points fall within a certain numeric range (ie less than 100 but greater than 50) but I want to be able to reference a specific cell containing the criteria rather than using '100' or '50' in the formula. In my worksheet 50 is in cell I2 and 100 is in cell K2. Try this: =COUNTIF(D11:D15,"<"&K2)-COUNTIF(D11:D15,"<="&I2) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Kim B." <KimB@dis...

If Tuesday subtract 4 (Formulas)
I need to subtract 4 days if Tuesday, 3 days if Monday, 2 days if Sunday and 1 day if Saturday. I would like to include this with the formula I'm using now. (E2-7) the cel the formula is in and the cell i'm subtracting from are both formated as dates (11/18/09). Can anyone point me in the right direction? Thank you, Hi, =E2-CHOOSE(WEEKDAY(E2,2),3,4,0,0,0,1,0) Mike "Buschwack" wrote: > I need to subtract 4 days if Tuesday, 3 days if Monday, 2 days if Sunday and > 1 day if Saturday. I would like to include this with the formula I'm using ...

Using the wildcard with IF
Hi all, I'm trying to set up a message that will generate from an IF formula. At the moment I'm at: =IF(E3="*C3","","Please check that you have chosen") The content of C3 is a part of the content in E3 e.g. E3=161426 oxygen and C3=oxygen. I want to check that what is in C3 is also in E3 and, if not, put up a message. All help appreciated. Try it like this: =IF(COUNTIF(E3,"*"&C3),"","Please check that you have chosen") -- Biff Microsoft Excel MVP "DamienO" <DamienO@discussions.microsoft.com> wr...

IF and how to use it
In a spreadsheet I have columns that perform different parts of a calculation, these are all brought together in one final formula. However, it is possible for one column to return negative values - but I want to ignore these and only use any positive values that are returned. IF 'T2'<0 seems to make sense as a beginning, where T2 is the first cell containing a value. But where do I go from there, what makes a viable ending? TIA Dave Dave The IF function has three parts, seperated by commas... =IF(Comparison, What to do if true, what to do if false) Your formula might look li...