Solved

Excel formula question

Posted on 2013-01-27
12
462 Views
Last Modified: 2013-01-27
In the attached, cell L20 has the formula: =+K20/$J$95

Can anyone tell me how to understand what it's doing?
How the heck is it getting 3.37%?

Thanks
0
Comment
Question by:Miles Thornton
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 10

Expert Comment

by:Tony Barkdull
Comment Utility
Sorry, don't see the attachmnet.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
file
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, Miles_Thornton.

2850 divided by 96 is 0.033684211. However, the cell is formatted as a percentage, so what Excel displays is the amount multiplied by 100 with a % sign appended. (Oh, to two places of decimal.)

Edit: If you change the cell's format back to General then 0.033684211 will be displayed.

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Miles Thornton
Comment Utility
How did it get 2850?

I'm looking for help in understanding the significance of:
•the "+" preceeding the cell designator "K20"
•the "$J$95" portion of the formula

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
The + at the beginning does not have any effect on the result

The $ signs keep the address fixed. Will not change when copying the formula to another cell.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Miles_Thornton,

$J$95
The dollar signs are called "absolute addressing" and they mean that if you copy and paste the cell to another row/column then it will still continue to refer to cell J95. (Because "K20" has no dollar signs, it would change.

+
This has no effect. I've noticed that people with a Lotus 1-2-3 background (or, bizarrely, accountancy) sometimes use it.

Regards,
Brian.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:Miles Thornton
Comment Utility
ssaqibh and Brian - Thank you very much for your time and answers.

Given that the 2850 is the sum of column J

Am I correct in saying that teh formula: =IF(K20="","",+K20/$J$95)
means: When K20 is not empty, devide what's in cell K20 by the sum of column J?
0
 
LVL 26

Accepted Solution

by:
redmondb earned 200 total points
Comment Utility
Miles_Thornton,

Well, as long as J95 holds the sum of the column, correct!

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Miles Thornton
Comment Utility
Yep - confirmed.

Thanks guys!

Since Brian's answer was more complete; I'm awarding him the points.
0
 
LVL 1

Author Closing Comment

by:Miles Thornton
Comment Utility
Excellent interactivity and timeliness! Thanks Brian!
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Miles_Thornton!
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Yes that is correct, though it's not dividing by the "sum" of J95 but by the value stored in J95. (But the sum of one cell and the value of one cell are the same) And you can omit the + sign. It does not do anything and can only serve to cause confusion when reading the formula.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now