Hi all, Could anyone suggest a way to rationalise this formla: =IF(AT3="",IF(AQ3="","",(IF(AR3="?","",(IF(AR3="OFF LICENSE","",((IF(AR3="RECALLED","",AM3+(AR3*30))))))))),"") Or point me in a direction I should be looking Thanks in advance -- Steve I don't deserve this award, but I have arthritis and I don't deserve that either. - Jack Benny

0 |

3/28/2010 2:56:55 PM

What's the problem? Is it not working? What do you want "rationalised"? Regards, Fred "Steve" <stevebakerj@bigfoot.com> wrote in message news:fLidnfnLk7Ol8zLWnZ2dnUVZ8lidnZ2d@bt.com... > Hi all, > > Could anyone suggest a way to rationalise this formla: > > =IF(AT3="",IF(AQ3="","",(IF(AR3="?","",(IF(AR3="OFF > LICENSE","",((IF(AR3="RECALLED","",AM3+(AR3*30))))))))),"") > > Or point me in a direction I should be looking > > Thanks in advance > -- > Steve > > I don't deserve this award, but I have arthritis and I don't deserve that > either. > - Jack Benny

0 |

3/28/2010 3:01:53 PM

=IF(AT3="",IF(OR(AQ3="",AR3="?",AR3="OFF LICENSE",AR3="RECALLED"),"",AM3+(AR3*30)),"") -- HTH Bob "Steve" <stevebakerj@bigfoot.com> wrote in message news:fLidnfnLk7Ol8zLWnZ2dnUVZ8lidnZ2d@bt.com... > Hi all, > > Could anyone suggest a way to rationalise this formla: > > =IF(AT3="",IF(AQ3="","",(IF(AR3="?","",(IF(AR3="OFF > LICENSE","",((IF(AR3="RECALLED","",AM3+(AR3*30))))))))),"") > > Or point me in a direction I should be looking > > Thanks in advance > -- > Steve > > I don't deserve this award, but I have arthritis and I don't deserve that > either. > - Jack Benny

0 |

3/28/2010 3:15:11 PM

Hi Fred, No its works just fine, what I want to do is add more IF's to it e.g. a check for "DECEASED", so I'm trying to see if I can simplify it before I start making it bigger. Thanks -- Steve The key to being a good manager is keeping the people who hate me away from those who are still undecided. - Casey Stengel Fred Smith penned: > What's the problem? Is it not working? What do you want "rationalised"? > Regards, > Fred > "Steve"<stevebakerj@bigfoot.com> wrote in message > news:fLidnfnLk7Ol8zLWnZ2dnUVZ8lidnZ2d@bt.com... >> Hi all, >> Could anyone suggest a way to rationalise this formla: >> =IF(AT3="",IF(AQ3="","",(IF(AR3="?","",(IF(AR3="OFF >> LICENSE","",((IF(AR3="RECALLED","",AM3+(AR3*30))))))))),"") >> Or point me in a direction I should be looking >> Thanks in advance >> -- >> Steve >> I don't deserve this award, but I have arthritis and I don't deserve that >> either. >> - Jack Benny

0 |

3/28/2010 3:19:22 PM

Bob Phillips penned: > =IF(AT3="",IF(OR(AQ3="",AR3="?",AR3="OFF > LICENSE",AR3="RECALLED"),"",AM3+(AR3*30)),"") Hi Bob, Thanks again, works as expected and I can add to it as I wish without undue complication. -- Steve Sometimes I get the feeling the whole world is against me, but deep down I know that's not true. Some smaller countries are neutral. - Robert Orben

0 |

3/28/2010 3:29:26 PM

"Steve" <stevebakerj@bigfoot.com> wrote: > Could anyone suggest a way to rationalise this formla: > =IF(AT3="",IF(AQ3="","",(IF(AR3="?","",(IF(AR3="OFF > LICENSE","",((IF(AR3="RECALLED","",AM3+(AR3*30))))))))),"") What do you mean by "rationalize"? Do you mean "simplify"? If so, then: =IF(OR(AT3<>"",AQ3="",AR3={"?","OFF LICENSE","RECALLED"}), "", AM3 + AR3*30) ----- original message ----- "Steve" <stevebakerj@bigfoot.com> wrote in message news:fLidnfnLk7Ol8zLWnZ2dnUVZ8lidnZ2d@bt.com... > Hi all, > > Could anyone suggest a way to rationalise this formla: > > =IF(AT3="",IF(AQ3="","",(IF(AR3="?","",(IF(AR3="OFF > LICENSE","",((IF(AR3="RECALLED","",AM3+(AR3*30))))))))),"") > > Or point me in a direction I should be looking > > Thanks in advance > -- > Steve > > I don't deserve this award, but I have arthritis and I don't deserve that > either. > - Jack Benny

0 |

3/28/2010 4:10:41 PM

I need a formula that would allow me to copy one cell to another as well as remove the first three characters. example cel a - 12321232 would copy to cell be as - 21232 If anyone could help me that would be soo awesome Hi, Try one these =RIGHT(A1,5) If the lenght of your data is fluctuating then =RIGHT(A1,LEN(A1)-3) If you need them to return a number instead of text then tack *1 on the end of them. HTH Martin "RBD" <rdickiejr@gmail.com> wrote in message news:bab5ed6a-ba7d-48e9-8cc0-ad7d05e7ac73@r15g2000prh.googlegroups.com... >I need a formula that would allow me...

hi all can someone help me where i have a row of numbers that go 0, 0, 0, 0, 0, 5, 10, 5 etc... from cells D2:K2 i want a formula to put in A2 that returns the first non- zero number, ie. it would return 5. it would also be useful to have a formula that returns which column the first non-zero number occurs in. any ideas? thanks JohnQ one way: First non-zero (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(rng,MATCH(TRUE,rng<>0,FALSE)) where rng is your range reference (e.g., D2:K2). Column the first-non-zero occurs in (also array-entered): =LEFT(ADDRESS(1,3+MA...

I am using the following format to query another sheet and return data. =(SUMIF('Assignments Formulas'!B$5:B$251,B4,'Assignments Formulas'!R$5:R$251))/2 It works great for part of my needs, but not for all. In the next column I need to do essentially the same thing plus divide R$5:R$251 by Q$5:Q$251 but everything I have tried comes back as an error. I'd really appreciate some help with the syntax since I am pretty sure it can all be done, but I just don't know what I am doing! If not- I realize I can add additonal columns and then hide them to simplify the fo...

I have a simple "if/then/else" formula that I would like to display the result of the "else" portion with no decimal places showing, instead of the default value of the cell. Can I include the format in the formula? Here is my formula: =IF(D12>75,D12/E12,D12) Thanks, -- Zilbandy - Tucson, Arizona USA <zil@zilbandyREMOVETHIS.com> Dead Suburban's Home Page: http://zilbandy.com/suburb/ PGP Public Key: http://zilbandy.com/pgpkey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Hi, Try =IF(D12>75,D12/E12,INT(D12)) or replace Int with roun...

Excel 2003. I'd like to change an entire range of cell formulas from relative to absolute. Mark You would need VBA to make global changes to cell references. Here are four........ Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) ...

Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

I am trying to make a barcode that will enter a static time into a cell and move to the next cell (right) I have 3 of 9 barcode fonts. There is no = sign nor is there a symbol for it. Does anyone know how to do this. Other choice would be a button on a toolbar but would like to not have the person return to the computer each time to enter. Any help please. pgriff -- pgriff ------------------------------------------------------------------------ pgriff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26189 View this thread: http://www.excelforum.com/showth...

I received a workbook from another company and wanted to add a new column of formulas. However, even though I included the = sign and ensured the correct formula, my entry into the cell would suddenly convert it to text, showing a ' or ^ character in front of the = sign. I discovered if I clear all formatting and data entry from the cells, it would then allow me to enter formulas. I've also found that if I create new workbooks/spreadsheets, this does not happen. I experience the same odd behavior in 2003 and 2007. This suggests there's something wrong with the file the other compan...

In COL A, I have a list of 2 and 3 digit charactors. I would like to seperate each string into the three adjacent columns. KQ AJs TT 89s KQ K Q AJs A J s TT T T 89s 8 9 s I know that I can use the Left function for the first col, but cant figure out how to get just the middle or third letters. Thanks in advance, Andrew Check your last post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------...

hi i am wanting to merge data from 1 worksheet with another how do i do this? Copy/paste then manipulate. OR provide more detail on your layout and type of data to merge. Gord Dibben Excel MVP On Wed, 16 Mar 2005 11:33:04 -0800, "dd" <dd@discussions.microsoft.com> wrote: >hi i am wanting to merge data from 1 worksheet with another how do i do this? hi there thanks for your reply. i do not have exact data. it is for an interview question how do you merge spreadsheet A and spreadsheet B using excel ? and then how do you identify say for instance someones name who is ...

Is it possible to set formulas to deactivate after a given period o time? I email out a number of spreadsheets that have taken many hours/days t assemble to clients. If they choose to change suppliers (as one recentl did) they can simply use my spreadsheets and change the logos and th new supplier gets all the benefits without the pain! The value of the spreadsheets (the useful life of the data) is only few days so it would be useful if the formulas stopped working afte maybe a month. Any ideas/suggestions would be appreciated Ton -- y_no ------------------------------------------------...

Hi all: I was wondering if there was a way to put a conditional formating on a cell based on the values of another cell. What I mean is that, if Cell A is >5 , make cell b Blue, not just cell A. Likewise, if Cell A is <5, make both cell a and b red. On the same topic, is it possible to put more than 3 conditions on a conditional formating? Thanks, Steve o This might point you in the right direction. Sometimes you need a row formatted according to the item in A1. Use a formula in the conditional formatting in type in something like this... =$A1<5 This will apply the cond...

Is there a function or value I can use in a cell formula that would put a null value in the cell? For example, suppose my formula is =if(A1=1,<null value>) and suppose this condition (A1=1) is met. If I copy this cell to the clipboard and then special-paste it to another cell for just "values", then I want nothing (empty!) to be put in the target cell. =IF(A1=1,"","do something else") since you didn't say what you want if the condition is false -- Regards, Peo Sjoblom "Hall" <hall@garp.org> wrote in message news:eyEAPNywDHA....

Worksheet One contains survey data pertaining to customer satisfaction for all of our building communities. The data is entered in each row as follows.. .. community name, lot number, buyer name, etc. Therefore, this worksheet contains all the survey results for all of our buyers, and then based on the survey responses, an overall rating is calculated. I would like to then link each row to its corresponding worksheet per community. By doing this, I can calculate the survey ratings per community as opposed to the overall rating calculated on worksheet One. I would greatly appreciate any as...

The fomula is a mix of VLOOKUP and SEARCH: =VSEARCH(A1;table1!A:A;2;false) In the A1 is the Text “Shell GmbH” In the A2 is the Text “Deutsche Shell” In the A3 is the Text “Shell LTD.” In Table1 Colum A it says Shell In Table1 Colum B it says Shell Group The result would be that the formula would give back always “Shell Group”. The option “true” would even accept “Shel” or “Schell” Thanks, Eckhard ...

I received an email with 10 attachments, and I want to forward 5 of them to someone. How can I select the attachments and send in a new email? I am using Outlook 2003. In Outlook 2000, I can do <Ctrl> + highlight the attached files, but can't do it in 2003. Any suggestions? "Tiffany" <tiffany@vpvp.com> wrote in message news:%23azaniUFFHA.624@TK2MSFTNGP15.phx.gbl... >I received an email with 10 attachments, and I want to forward 5 of >them to > someone. How can I select the attachments and send in a new email? I > am > using Outlook 2003. In...

I have no problem getting totals for Columns B, C, and D. =SUM(B3:B261) =SUM(C3:C261) =SUM(D3:D261) =SUM(E3:E261) These formulas total my columns And I can get a total across ONE Row at a time =SUM(B3:E3) this will total one row across into Column F But I don't want to type in the formula for 261 rows in Column F. Any help on how to do this would be appreciated. -- Cerridwen "Critical thinking demands we question the unproven, not that we meekly accept it." Diane Richardson For Stats on Scn go to: http://www.truthaboutscientology.com/stats/ For News on Sciento...

I want to copy formulas without changing the cell references and not relative without the $ signs. Example: In C1 I have =A1+B1 (actually mine is complex). I want to copy it as =A1+B1 to D1 D2 D3 (and many more) is there a clever way to do this. Thanks Mark Mark One way: 0. Make a copy of cell C1 by selecting C1. Select the formula *in the formula bar* and press <Ctrl>c and <Esc> Put the formula in a unused cell with <Ctrl>v 1. Make all references in C1 absolute, i.e. =$A$1+$B$1 (Use <F4> for a fast way of doing it, i.e. in the form...

I wish Microsoft stopped developing "helpful" programs. All their "auto" stuff leads to nothing but problems. Here is an example: I have to process data which come as CSV files and contain entries that look like multiplication e.g "3*4". Damn Excel can't handle these. Every chance it gets, it turns them into "=3*4" and displays "12". Any one knows how to kill this behaviour? Please share the knowledge. Henryk Birecki Henryk Birecki wrote... >I wish Microsoft stopped developing "helpful" programs. All their >"auto&q...

I'm a new MSMoney user and just set up a new bank account. I wrote a check from one existing account to the new account in a different institution. How should I catagorize the transfer of funds? MSMoney, in the budget tracking, etc. seems to show the the transfer as income (but to me it's not new dollars). Any suggestions? In microsoft.public.money, PMHicks wrote: >I'm a new MSMoney user and just set up a new bank account. I wrote a check >from one existing account to the new account in a different institution. How >should I catagorize the transfer of funds? ...

Hello Folks, I need a little help with what I think is a simply problem, but it is over my head. I need a formula that will multiply to cells when an x is placed in another cell. I would like a formula in cell H20, that when someone places an X or x in cell C20, multiply cell D20 by cell F20, with the answer in H20. Thanks for you time! Jess Jess, put this in H20 , =IF(C20="x",D20*F20,"") -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! U...

[Excel 2003] I have sixteen different conditions which can exist. These relate to whether certain aspects are TRUE or FALSE. When taken together I have 16 unique "cases". These appear in cells A1:A60 and are labeled Condition1, 2, 3 etc... Condition16 Depending on the condition in each cell in column A, I need certain formulas to operate in adjacent cells in colomn B and C For example for Condition1 in A1, I need the formula in B1 to be "=D1*E1" and the formula in C1 to be "F1*G1/H1" Each formula referes to cells in the same row, so perh...

Today at work I was writing an Excel formula that solves a cubic formul (i.e. the very advanced, tedious version of quadratic formula where cubic term is also in the equation). Anyway, small errors continually frustrated my attempts to correctl write what is basic algebra, it just requires on the order of 50 or 6 parentheses. So my question: Has someone devised an excel version of the cubic formula? or: Is there a program that automatically writes Excel algebraic formula from some other program where it is easier to write comple mathematical statements? Thanks! Jos -- Message posted f...

Hi, relative newbie question: I've got a datasheet form which I'm using for stock in/out recording which adds a record for each stock transaction in a stock table (number of stock in and out etc.) , I also have a total stock level field on the products table - I want to add\subtract from the stock total in the products table when I add a record to this stock transactions table in the datasheet - what is the best method to do this? I was thinking do a sql update in code on the Form_AfterInsert event - am I doing the right thing? -- Jyan ------------------------------...

I use a spreadsheet with a column (H) in which single digits (usually 0 through 6) are entered beginning in row 11. The most recent entry is in row 804 and there will be an unknown number of additional entries. I need a formula which will show the number of times that a certain value (for example, 2) has been entered in the column. In addition, it would be helpful if a second formula could show the result as a percentage of the total number of entries in the column. Any tips on how to proceed? I=92m using MS Excel 2003. Thanks for any assistance. GrtArtiste On Feb 19, 6:55=A0am, GrtArtiste...