Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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)? Regrads, Corey Brock |
#2
|
|||
|
|||
=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" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote in message ... 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 . |
#4
|
|||
|
|||
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 :-) ---------------------------------------------------------------------------- wrote in message ... 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" wrote in message ... 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 . |
#5
|
|||
|
|||
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 :-) ---------------------------------------------------------- ------------------ wrote in message ... 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" wrote in message ... 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 . . |
#6
|
|||
|
|||
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" wrote in message ... 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 :-) ---------------------------------------------------------- ------------------ wrote in message ... 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" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |