Solved

# Excel formula question

Posted on 2013-01-27
462 Views
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
Question by:Miles Thornton
• 4
• 4
• 2
• +2

LVL 10

Expert Comment

Sorry, don't see the attachmnet.
0

LVL 43

Expert Comment

file
0

LVL 26

Expert Comment

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

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

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

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

LVL 1

Author Comment

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

redmondb earned 200 total points
Miles_Thornton,

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

Regards,
Brian.
0

LVL 1

Author Comment

Yep - confirmed.

Thanks guys!

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

LVL 1

Author Closing Comment

Excellent interactivity and timeliness! Thanks Brian!
0

LVL 26

Expert Comment

Thanks, Miles_Thornton!
0

LVL 50

Expert Comment

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

### Suggested Solutions

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.