Solved

Excel - Order of Precedence

Posted on 2006-11-29
13
1,141 Views
Last Modified: 2011-09-20
Hello:

Can anyone please explain or help?  I enter following formula in Excel:

=B5*B6+C3/D4^2

According to the order of precedence, it's supposed to calculate or evaluate the exponent D4^2 first but it's not doing that.  It first calculates B5*B6 and then it calculates the exponent and then it divides C3 by the result of D4^2 and finally adds.  

Can anyone please suggest why Excel's doing this?  

0
Comment
Question by:navaqeel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 16

Assisted Solution

by:AdamRobinson
AdamRobinson earned 50 total points
ID: 18043287
Perhaps I am misreading you, but how would your description of what occurs in any way provide a different answer if it calculated the exponent first?

In 1st case you get:

B5 * B6 + (C3 / (D4^2))

In the 2nd case you get:

B5 * B6 + (C3 / (D4^2))

Are you actually trying to raise c3/d4 to the 2nd power?

0
 
LVL 16

Expert Comment

by:AdamRobinson
ID: 18043291
If so, you should probably try: (C3 /  D4)^2

Otherwise, after re-reading what you wrote 10 times now, I can't see how it is doing anything wrong.  
0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 18043294
navaqeel,

Actually it is handling the exponent first.  It just isn't part of the B5*B6.  The exponent is involved in the part C3/D4^2.  It is done before the division as you mentioned.  Once all of this is done then the addition is done.

This page (http://www.informit.com/articles/article.asp?p=328639&seqNum=2&rl=1) has a nice table that shows the order.

Let me know if you have any questions or need more information.

b0lsc0tt
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 18043320
AdamRobinson may have answered all of your question.  My post has some different info but is pretty much a late duplicate.  If you are watching Excel do this step by step then and question that then it is the rule of working left to right.  If the exponent was with B5*B6 then it would have been done before the multiplication.

bol
0
 

Author Comment

by:navaqeel
ID: 18043379
Thanks for all suggestions guys.  when i evaluate this formula using the "Evaluate Formula" button on the Auditing tool bar, it clearly displays that the multiplication between B5 * B6 takes place first, then the exponent D4^2 gets evaluated then the division and finally the addition.  

Try this out yourself.  Enter some numbers in B5, B6, C3, and D4 and then type the following expression as it is:

=B5*B6+C3/D4^2

No parenthesis have to be applied in the above formula.  I just can't understand why B5*B6 gets evaluated first.  
0
 

Author Comment

by:navaqeel
ID: 18043395
Try this too:

=B5*B6+C3/B5+D4^2

In the above expression, the exponent will be calculated at the end even after addition. Is something wrong with my Excel ???
0
 
LVL 54

Accepted Solution

by:
b0lsc0tt earned 350 total points
ID: 18043419
The parenthesis you mentioned are being "assumed" by Excel.  It is working from left to right.  As it looks then it see the formula as (B5*B6)+(C3/(D4^2)).  It is a logical order that sees + as a lower precedence order and then allows it to process the previous higher order.  Without any precedence order and just the rule to work from left to right then the formula would need to be written as I showed it.  The second formula you showed (the one with 2 + signs) would also follow these steps.  B5*B6 and then C3/B5 can be processed as Excel moves from left to right.  Nothing is wrong with your Excel program or the way it works.

I hope this clarifies what is happening.  Let me know if you still have questions.  The key is probably to remember that working left to right is the first rule.  Once a lower order of precedence is found then the "block" can be calculated.  It doesn't matter what is on the other side.

bol
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 18044090
In mathematics the basic principle of "BODMAS" is applied.
This should also transfer to Excel.

BODMAS defines the order of what is carried out.

B - brackets
O - order (or exponent)
D - division
M - multiplication
A - addition
S - subtraction

for example - what is 2 + 3 x 5?

Apply the left to right rule - gives 25. Incorrect.
Apply BODMAS gives 17. Correct.
Try it in excel.

Here's a link
http://www.easymaths.com/What_on_earth_is_Bodmas.htm
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 18044155
So transferring to your equation  "=B5*B6+C3/B5+D4^2"
(e.g. - B5=3,  B6=8,  C3=6, D4=2)
gives us

D4^2  = 4
C3/B5  = 2
B5*B6  = 24

all added up = 30
0
 
LVL 16

Expert Comment

by:AdamRobinson
ID: 18045873
I should have realized you were just asking theoretically.  The reason the formula does this was addressed by b0l above.  Excel does make assumptions about multiplication and parentheses.  The simple reason is there is no substantive difference between:

a * b and (a * b)

The better reason for doing so (keeping it mind it operates left to right!) is because

(a * b) + (c * d ^ 4)

really does need parentheses around the first part in order to be clearer.  In your case, it's not as big of a deal, but think of this.

(a * b) + (b + a + c / 4) - (d + e + f) + (g) + (h+a)

Without the parentheses, a human might begin to stare there.  Excel, IIRC, can provide parentheses' suggestions.  Given this is the case, it likely is just built in to assume parentheses exist for the human using the program.  

0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 18046829
@SeriousNick - I may not have been clear enough when I tried to answer the question and mentioned Excel was working from left to right.  What I meant to describe still looks at the signs to calculate.  In your example (2 + 3 * 5) it would see the multiplication sign, as it moved from left to right, and not calculate, since the precedence was higher.  The calculation would be correct.  The method my comment described would not yield 25.  I'm sorry if that wasn't clear in what I typed and thanks for giving me a chance to clarify it.

bol
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 18047548
Hi bol

Thats great.
I was simply mentioning the acronym BODMAS (which is taught at elementary levels of education) contrary to popular belief that one should calculate from the left first.
This "rule" has been adapted by electronic calculations too, and therefore is the basic principle used when a formula/equation has been poorly written (without parentheses for example).
Regardless of how Excel "appears" to be doing the calculations - it should always follow the above priniciple.
It works for kids of a very young age and can still work for us "older" folks......  Simple to remember (which becomes so much more helpful as time ticks by ...).

No offence intended whatsoever so hopefully none taken.  :)

Although, in re-reading, I have to admit >>"The key is probably to remember that working left to right is the first rule."<< in your post was misleading so glad we had a chance to clarify the position on that.

And now I think the questioner has more than enough information.  ;)

Regards to all!!
0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 18078288
I'm glad that I could be one of those that helped you.  Thank you for the grade, the points and the fun question.

bol
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question