Formula Help #40

 am using a way to do the following

column1              column 2             column 3
(2+3)+(3*5)        5+15                    20


in the first column I want to be able to see the expresion
in the second column I want to have the result of each parenthesis
and in the final column the result

Please help
Thanks
Stelios



0
stelios (2)
2/22/2008 3:15:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
215 Views

Similar Articles

[PageSpeed] 17

Stelios,
 Here is one solution:
Break down your equation so each part is in it own column.
Column A   Column B Column C     etc.
    (                 2            +             etc.
 
Somewhere on the same worksheet list the numbers(0 -9) and the 
operators (*, /, +, - ). I used R3:R12 for numbers and S3:S6 for
operators.
Column A   Column E  Column G   Column K
     (                )              (                )
Column B, C, D, F, M
Use Validation to do a drop down "List".

Column L uses an "IF statement"
"=IF(C2="*",(B2*D2),IF(C2="/",(B2/D2),IF(C2="+",(B2+D2),_
IF(C2="-",(B2-D2),0))))"  Leave off the first " and last ". The "
inside the IF statement are necessary.

Column N uses an "IF statement"
"=IF(I2="*",(H2*J2),IF(I2="/",(H2/J2),IF(I2="+",(H2+J2),_
IF(I2="-",(H2-J2),0))))"  Leave off the first " and last ". The "
inside the IF statement are necessary.


Column P uses an "IF statement"
"=IF(M2="*",(L2*N2),IF(M2="/",(L2/L2),IF(M2="+",(L2+N2),_
IF(M2="-",(L2-N2),0))))"   Leave off the first " and last ". The "
inside the IF statement are necessary.

You can copy this down, and change the numbers and the operators
and the answer in column P will change with the ne information.

hth

"Stelios" wrote:

>  am using a way to do the following
> 
> column1              column 2             column 3
> (2+3)+(3*5)        5+15                    20
> 
> 
> in the first column I want to be able to see the expresion
> in the second column I want to have the result of each parenthesis
> and in the final column the result
> 
> Please help
> Thanks
> Stelios
> 
> 
> 
> 
0
FloMM2 (102)
2/24/2008 5:27:00 AM
This will do it.  Let me know what grade we got.

Sub Main()
  Dim ch$, strOld$, strNew$, lenTmp%
  Dim iPos%, iPosLParen%, iPosRParen%, iCol%
  iCol = 2
  strOld = Cells(1, 1).Value
  Do
    strNew = ""
    iPosRParen = 0
    For iPos = 1 To Len(strOld)
      ch = Mid$(strOld, iPos, 1)
      strNew = strNew & ch
      If ch = "(" Then
        iPosLParen = iPos
      ElseIf ch = ")" Then
        iPosRParen = iPos
        If iPosLParen <> 0 Then
          lenTmp = iPosRParen - iPosLParen + 1
          strNew = Left$(strNew, Len(strNew) - lenTmp) & _
          Application.Evaluate( _
            Mid$(strOld, iPosLParen + 1, lenTmp - 2))
        End If
        iPosLParen = 0
      End If
    Next iPos
    If iPosRParen = 0 Then strNew = Application.Evaluate(strNew)
    Cells(1, iCol) = "'" & strNew
    iCol = iCol + 1
    strOld = strNew
  Loop While iPosRParen <> 0
End Sub ' Dave D-C

"Stelios" <stelios@hotmail.com> wrote:
> am using a way to do the following
>column1              column 2             column 3
>(2+3)+(3*5)        5+15                    20
>in the first column I want to be able to see the expresion
>in the second column I want to have the result of each parenthesis
>and in the final column the result
>Please help
>Thanks


0
dacromley (19)
2/25/2008 1:14:46 AM
Reply:

Similar Artilces:

HELP
I'm programing with Microsoft Access 2003 an Invoice, so I have 2 Tables Header and Lines both linked by the field InvoiveNumber. On the "Detail" part of the form: First are the data related with the Client and Invoice (InvoiceNr(autonumber), Invoice date, Client Name,....) Second it displays a subform related to lines of the Invoice, that is the Products, price, quantity, value,... At last the Vat tax and the total of the invoice and a button to print the Invoice that is nothing more then a report. Everything works fine BUT, there is always a BUT, the Vat tax and Inv...

Formula troubles
I am having trouble getting this formula to work, can anyone help m out? This is what I have: =IF(Z4:AC4,AH4:AJ4={FAIL},"YES","NO") I am trying to show: If Z4 thru AC4 and AH4 thru AJ4 are equal t "FAIL" display "YES" otherwise display "NO". Any help is much appreciated! Biff really hooked me up last time I ha a formula roadblock...Biff, are you out there? -- egeorge ----------------------------------------------------------------------- egeorge4's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3025 View this t...

Incorrect result for simple SUM formula
Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D12:D22) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V What is the values you are having in D12 to D22? -------------------- (Ms-Exl-Learner) -------------------- "VivienW" wrote: > Hello, > > Does anyone know why E...

formulas #5
I need a formula for a tax calculation. Example - income at 400000 with tax rates of 18.62% for the 1st 200000, 20.87% for next 100000 and 25.39% for next 100000. I want to show the 3 separate tax amounts in different cells. I want a formula starting from the 400000 amount in the taxable income cell to show the following in the 3 cells. ie: taxable income 400000 200000@18.62 32240 100000@20.87 20870 100000@25.39 25390 Hi Terry, First off, 200000*.1862 = 37240 (32240) typo? B1 taxable income 400000 B2 =IF(B1>=200000,200000*0.1862,B1*0.1862) B3 =IF(B1<200000,0,IF(B...

help moving to new exchange server
Hi i have hopefully a quick and easy question. we are in the process of migrating our exchange 5.5 server, to exchange 2003. what i need as a quick easy way to modify everyones outlook profile to point to the new server. the new server is in a whole new org, and with a new name. we are using outlook 98,2000,xp, and 2003 i'm leaning towards using the .prf files, but what i am hoping is that someone else has done this and has some scripts already made :) Thanks for any help anyone can give. Where do you get stuck with creating a prf-file? A better way to do it is by using the foll...

First deployment of .net winforms help needed
Hello, I've never had to deploy a .net solution (winforms) before. In the past (with COM) I'd have to register any dlls with regsvr32. Now with .net we don't have to do that but I'm not sure exactly how to deploy a .net solution. If I compiled a visual studio project in .net 3.5 I need to make sure the destination server is running .net 3.5. Then I just use xcopy to copy the exe and dlls into any directory and that's it? What if I wish to put the dlls in a separate directory? how will the exe find it? I'd really appreciate any help or links on how to dep...

Array formula based on Chip Pearson's lookup data samples
I have an array formula based on Chip Pearson's lookup data samples : =INDEX('Raw Material'!H3:H500,MATCH(SMALL(ABS('Raw Material'!H3:H500-B83), {1;2;3}),ABS('Raw Material'!H3:H500-B83),0)) From the value in "B83" it returns 3 nearest match values from my 'RawMaterials' sheet. In the columns next to the 3 values returned I have left lookup formulas that match the names from 'RawMaterials' to the values. 1st Problem : It all works well until I get duplicate values then I end up with 3 values the same instead of the 3 nearest matches. 2...

please help with this issue
I have a workbook with approx. 25-30 worksheets in it with the last being an overview of the others. I use this workbook to track some basketball stats. What I need to do is to: countif cell A9 of sheet 1 thru sheet 30 are populated with a text letter (P for played), the resultant of the countif will be used to divide into an overall sum of points - thus giving an average points per game. Any suggestions Thanks Dudley Hi Dudley if you list the names of your sheets in an area of the summary worksheet (e.g. A1:A30) you can use the following formula =SUMPRODUCT(COUNTIF(INDIRECT("'...

Public Folders missing after going from Exchange 5.5 to Exchange 2003!!! HELP!
Ok - this is what happened. I was following along in MS KB #316886, and I was on page 5 where it talks about the migration of Public Folders and Mailboxes. What I did was (I didn't read the directions carefully, I know) I went to move the Public Folders to the new 2003 Exchange server. I went to the 5.5 Admin program, and went to the Public Folder that I wanted to move, and I went to Properties, and the Replicas Tab. I misunderstood the directions. So basically, how it was set up was: BEFORE: Servers: Replicate Folders To: EXCH3 EXCHANG EXCH2K3 AFTER: Servers: ...

speeding up calculation: replacing array formula with database function?
Hi, I could use some advice on optimizing a formula. I currently have the following array formula in the cells of column D: {=MAX(IF($A$2:$A$13695=$A13695;$B$2:$B$13695;FALSE))} In words: take the highest value of column B for every cell in B where the value in column A is equal to the current value of A. Example: 01/04/2010 5937 5937 01/04/2010 5936 5937 07/04/2010 5943 5943 07/04/2010 5942 5943 As we all know, excessive usage of array formulas makes calculation painfully slow. As you can see I am now at row 13695 and rows keep getting added. ...

multiple sheets, need help please
I have a multiple sheet workbook. I want one cell on my second sheet to show either one cell or the other, depending on what is selected, from my first sheet. How can I do this? Thank you. Where you want that result to appear, create a formula using the IF function. The equation will be something like =if(a1=1,Sheet1!b4,Sheet1!c5). The first argument (up to the first comma) is the condition, a logical test. If it's true, then the result of the second argument will be the function's result; otherwise the third argument will be the result. The easiest way to enter the cell re...

After migrating from Exchange 5.5 to Exchange 2003 I get a connect problem -- Please Help!!
Sometimes a user tries to connect to the Exchange Server from Outlook a window pops up stating that exchange is in recovery mode and gives you a choice to to connect, wait, exit (something close to that). I would have thought that the recovery part by now would be complete but 2 months later still doing the same thing Thank You Will There is a name resolution problem on your network. Make sure that DNS suffix on server and clients do match, and DNS server can be contacted by user's machines. MS Newbie wrote: > Sometimes a user tries to connect to the Exchange Server from Outloo...

Efficient Formula?
Hi, I'm new to this newsgroup and newsgroups in general and I hope its ok to add snapshots to posts.... I created a sheet a while back part of which showed a monthly snapshot of when staff were working. The issue I had was that staff worked on a rotating 4 week roster not a monthly roster and it took me quite some time to get it right. As I said I've only just recently started using this newsgroup and have seen some very advanced and inventive solutions to problems. As such my question is this. Could I have achieved the results below using a more efficient formula or metho...

formatting a spreadsheet help
I have a few different dilemas going on here. First: Can a conditional format be set up in Vista or Windows 7 both 64 bit, and saved in 97-2003 excel saving the conditional formats? Second: I have a spreadsheet column A is my received date, Column B is Stock #, Column C is Serial #, Column G is my finished date. What I need to be able to do: When I enter information into Column C (Serial #), I need some type of notification if that same number has been entered within a six month time period from my Column G (Finished date). 1. Yes. However, there are some formats which only Ex...

help: how protect formulas in workbook?
Can we locked cells with formulas from editing in one step? When I select cells with formulas in all worksheets by selecting all worksheets and give properties "locked" in Format -Cells-Protection tab and when I try to protect worksheet by Tools-Protection-Protect Sheet is not available. Can we protect all worksheet in workbook in one step or we must do it sheet by sheet? Thanks. spanic -- spanic ------------------------------------------------------------------------ spanic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5105 View this thread: ht...

Excel formula in an expanding table
I'm calculating the gas mileage on our new car. As a result I have to change the formula to reference the last fillup date in my table. Is there any way I can make this formula automatically reference the last fillup date or row? I've looked at all the Exel functions and can't seem to find one that works. Thanks. Pete The following will return the last numerical data in a column, including dates which are stored as serial numbers... =LOOKUP(9.99999999999999E+307,A:A) Hope this helps! In article <43B78FF3-1FB4-47EA-9D74-F54813ED9349@microsoft.com>, "Keady"...

Formula to return numerical, text result?
I need to create a formula that based upon three possible factors, wil return one of two calculations or a text message. If cell B="Good", the formula in cell C returns "Text" If cell B="Better", the formula in cell C returns cell A*.3 If cell B="Best", the formula in cell C returns cell A*.5 Thanks in advance for any tips -- burgeo ----------------------------------------------------------------------- burgeon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2023 View this thread: http://www.excelforum.com/showthread.php...

help on reminders
i have a table named tblProduct and then there is a field MaturityDate.. what i want to happen is that every time the product reaches its maturity date, a pop-up form or some sort of reminder wuold appear everytime the database is opened.. please...any help would be much appreciated ... thank you in advance -- ai® Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 Check out: http://www.utteraccess.com/forums/showthreaded.php?Cat=&Number=1404836&page=&view=&sb=5&o=&vc=1 ai_enjoi wrote: >i have a table n...

For Each Next help
Here is my code. Dim C As Range For Each C In Range("TotalsCells").Resize(1, 0) -----IT FAILS AT THIS LINE Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)" Next C I have a couple of questions. Is my variable declaration correct? I want to inspect each cell in the range. Is my Resize syntax correct for inspecting each cell? Is my Sum syntax correct? Thanks in advance for your help. On Wed, 8 Oct 2008 10:06:18 -0700 (PDT), troy_lee@comcast.net wrote: >Here is my code. > >Dim C As Range > For Each C In Range("TotalsCells"...

Check box help in Word 2007
I have inserted check boxes into a document using the Check Box form field. The user has to go through the document, checking the boxes that apply in a particular client's situation, so it's different every time. I cannot lock the document because it is set up for a merge. Other than having to double click on the check box, then click checked, then click OK (phew!), is there a shortcut, a faster way to do check/uncheck these boxes? I seem to remember (perhaps it was an older version) that a simple double click used to check or uncheck the box. Which makes a lot more ...

HELP! Need to save in Excel 2.1 format
Hi I have excel 2007, and the datasystem here only accepts excel 2.1 format files. How can i convert or save in excel 2.1 format? I'd really appreciate any input, even link to some external convert program. Thanks Hi, Why you don't try to save it as excel 5.0 It should work when saving as scroll almost to the bottom there you will find excel 5.0 "Poppe" wrote: > Hi > > I have excel 2007, and the datasystem here only accepts excel 2.1 format > files. > > How can i convert or save in excel 2.1 format? > > I'd really appreciate any input,...

Excel Formulas 04-26-10
Dear, I need to calculate the below in excel sheet. please find below the details and advise me: Amount - 25000 - 3% * 3% please advise how to subtract 3% from 25000 and then calculate 3% of that amount. awaiting for your earliest response. Hi Shona, 3% is actually 3 hundreths or 0.03. Therefore formula is. =(25000-0.03*25000)*0.03 Standard maths rules apply inside brackets are first, multiplication is before add or subtract. -- Regards, OssieMac "Shona" wrote: > Dear, > > I need to calculate the below in excel sheet. please ...

Help me identify the symbols in "Icon" field of my Inbox in Outloo
How does one manage such a query in the simplistic "Help" offered by these people? Where can I find the answer to my question? "Icon" in the search and help facility treat it only as icon the symbol in Windows, not as a specific header in my Inbox in Outlook 2003, the Email program of Microsoft Office. How does one manage a post in which the user failed even to post a question? You force us to guess what it is. That wastes our time and yours. My guess as to what your question is would have this answer: http://www.howto-outlook.com/howto/icons.htm -- Russ Valentine [...

3 Formula Questions
Hi, I have three Excel 97 formula questions: 1) Suppose I have a formula which yields a dollar value with a theoretically infinite amount of decimal places, but I use column formatting to limit the decimal places to two positions. For example, the formula: =1/3 yields $0.33333333333333333333333333333 but I limit it to two places with column formatting: $0.33 But I then am multiplying the above results times a fixed quantity. But I need to multiply the fixed quantity times the two-place value, not the infinite place value. For example, I might need "5 times $0.33", NOT "5 ...

Changing cell from formula to data.
Is there a way to change a cell from a formula (that is returning data) to the data it is returning. I realy hop it it becuse it would help out a ton. -- Thanks TimM If you select the cell and copy then edit - pastes special - values on the cell you will have the value and not the formula. HTH -- Sincerely, Michael Colvin "TimM" wrote: > Is there a way to change a cell from a formula (that is returning data) to > the data it is returning. I realy hop it it becuse it would help out a ton. > > > -- > Thanks > TimM Hi, Select the cell(s) contain...