You are here:

Calculus/Cell Reference in Excel application.


QUESTION: Dear Prof Scott‎‎

Cell Reference is intersection of column,row where data is to be inserted in the cell.

For example : A3 Cell data is 5. Now A3 cell is column 1 and row 3.

Instead of addressing a cell as columnrow i.e. A3 would we had use addressing a cell as rowcolumn, it would have become 3A. Now 3A=5.

In this case, the cell reference is 3A, now if this convention had
been applied instead of the columnrow convention for cell representation, would it had impacted other excel features ?.

Examples : 5C, 4B, 9R instead of C5,B4,R9 respectively.

The Matrix form representation is m rows * n columns.

Example : The notation of a 3 * 2 matrix : A[3,2] where number of
rows = 3 and number of columns = 2. There are total six elements in the matrix. A[2,3] matrix will be 2 rows and 3 columns with number of six elements in the matrix.

In excel, cell representation is A3 where A = Column name and 3 = row number and not 3A. i.e. ColumnRow and not rowcolumn convention.

In this case, the cell reference is 3A, now if this convention had been applied instead of the columnrow convention, a rowcolumn convention for cell representation, would it had impacted other excel features ?.

Example :


if i write 3E instead of using E3 and R3C5 cell references
saying 3E=7

Is   3E=E3 ?
    3E=R3C5 ?

Awaiting your reply,

Thanks & Regards,
Prashant S Akerkar

ANSWER: As far as spreadsheets goes, the letter was put first back when the first spreadsheets came out since the computer automatically knew that this was a cell reference and not a number.

It knew that if I input D11, the was column D (4th column) and the 11th row.  If I had changed it to 11D, it would have required an extra step in the program to note that it was an address and not a bad number and that it had the number first.  Back then it started with 1, so it assumed it was a number.  It seems like nowadays, it does a little more work to see what it is.
The next digit was 1, so it then had 11.  The next digit would be D, and that's an error.

By the way, it would not recognize R3C5 unless you put =R3*C5.
Nowadays in Excel, the should be RC35, to refer to the 35th row is column RC.
If it was CR35, it would refer to the 35th element in column CR.
If it was C3R5, that might look like it was suppose to be =C3*R5.
As it is, it take C3R5 to just be some more text.

For a cell reference in Excel, we need to have an equal sign first to indicate it is a cell reference.  As a side note, the highest integer Excel takes on my PC is 99,999,999,999.
I have created spreadsheets that can handle even more digits by using basic mathematics.
For example, I have one that can multiply two 9 digits numbers and get a 18 digit number.
It will give me 999,999,999 x 999,999,999 = 999,999,998,000,000,001.  See, it takes the 999,999,999 and puts it in three cells in the 1st row and three cells in the 2nd row.
It then multiplies out like we learned in grade school to do with small numbers.
The sum below each column is taken as =mod(x,1000), and the sum for the next column is =sum([current column])+int([last sum]/1000).  Extending this to more digits would be easy.

I'm not sure you're interested in that, but it what comes across my mind.

In fact, in just a few minutes, I did 999,999,999,999 times itself and got

To do it again, I did 999,999,999,999,999 times itself and got
999,999,999,999,998,000,000,000,000,001.  In the spreadsheet, it doesn't have the 000 in each cell, but only 0.  What I did was use the concatenate() function and tests for the number to input extra 0's in front plus insert commas.  This gives me 999,999,999,999,998,000,000,000,000,001.  In the next cell, I put that - 1 and got 1E+30,
so it is still too many leading 9's, but it does recognize it as a number.

In America, written out, that's 999 octillion, 999 septillion,
999 hexillion, 999 quintillion, 998 quadrillion, and 1.

In other areas of the world it seems that it would be 999.999 trillion, 999.999 billion,
998 million, and 1. Isn't a period used for a comma, as in 1.234.567,89 is
1 million, 234 thousand, 567 and 89/100?  That would mean 999.999 would be
999 thousand and 999.  Right?

Maybe this is too much stuff?  I'm too much into numbers, you know.

---------- FOLLOW-UP ----------

QUESTION: Dear Prof Scott

Thank you.

As Excel is not allowing us to use 1A,5B,7R conventions  for a cell reference, if we tried using this cell referencing, it results in formulas or functions not accepting this cell reference.

To make this happen that Excel also accepts 1A,2B,5C cell references then one has to modify the source code of the latest version of excel. By doing this the current features should continue working as it was before.

Mostly the Source code of MS Excel application was written in C or C++ programming languages.

Thanks & Regards,
Prashant S Akerkar

It is easier to put letters in first since that way when the C++ language reads it in, it gets a character first and knows it is a cell reference.  If it was numbers first as a cell reference, that would require extra code.  For example, if it read an equal sign, it knows it must be followed by a number (which means the whole string is a number), a letter (which means it is a cell reference, or a double quotation mark (which means it is a character string).  On the last one, it drops the quotation marks and drops the equal sign, leaving the string only on the display, but keeping them up top when that cell has the cursor on it.


All Answers

Answers by Expert:

Ask Experts




Any kind of calculus question you want. I also have answered some questions in Physics (mass, momentum, falling bodies), Chemistry (charge, reactions, symbols, molecules), and Biology (reproduction, insusion of chemicals into bloodstream).


Experience in the area: I have tutored students in all areas of mathematics since 1980. Education/Credentials: BSand MS in Mathematics from Oregon State University, where I completed sophomore course in Physics and Chemistry. I received both degrees with high honors. Awards and Honors: I have passed Actuarial tests 100, 110, and 135.

Maybe not a publication, but I have respond to well oveer 8,500 questions on the PC. Well over 2,000 of them have been in calculus.

I aquired well over 40 hours of upper division courses. This was well over the number that were required. I graduated with honors in both my BS and MS degree from Oregon State University. I was allowed to jump into a few courses at college a year early.

Awards and Honors
I have been nominated as the expert of the month several times. All of my scores right now are at least a 9.8 average (out of 10).

Past/Present Clients
My past clients have been students at OSU, students at the college in South Seattle, referals from a company, friends and aquantenances, people from my church, and people like you from all over the world.

©2016 All rights reserved.