Excel formula question

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
LVL 1
Miles ThorntonSenior C# Developer and TFS AdministratorAsked:
Who is Participating?
 
redmondbCommented:
Miles_Thornton,

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

Regards,
Brian.
0
 
Tony BarkdullCommented:
Sorry, don't see the attachmnet.
0
 
Saqib Husain, SyedEngineerCommented:
file
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
redmondbCommented:
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
 
Miles ThorntonSenior C# Developer and TFS AdministratorAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
redmondbCommented:
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
 
Miles ThorntonSenior C# Developer and TFS AdministratorAuthor Commented:
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
 
Miles ThorntonSenior C# Developer and TFS AdministratorAuthor Commented:
Yep - confirmed.

Thanks guys!

Since Brian's answer was more complete; I'm awarding him the points.
0
 
Miles ThorntonSenior C# Developer and TFS AdministratorAuthor Commented:
Excellent interactivity and timeliness! Thanks Brian!
0
 
redmondbCommented:
Thanks, Miles_Thornton!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.