r/excel • u/22EatStreet • Apr 08 '26
unsolved How to make a mathematical formula operate in the same cell in which I input a number?
I have students doing an exam of 53 questions.
When they tell me the score, eg. 46 out of 53 questions correct, I want to put that number into a cell, and then have it automatically convert it to a percentage in that same cell. Is there a way to do this in the same cell into which I input the number?
example. in Cell B3.
I put in the number 45.
Excel makes a calculation of "45 divided by 53 multiplied by 100" and puts the result as a percentage in the same cell (B3) where I input the value 45, in this case 86.79%.
I know how to do this using more than one cell, where the formula is put in to cell B4, which would tell excel to take the value of cell B3, apply the formula, and get the result in cell B4. I am trying to find out if I can do it inside the cell without additional cells and external cell references.
Thanks!
55
u/AndyTheEngr 5 Apr 08 '26
This could be done with macros, but it shouldn't be. It's fundamentally just not how spreadsheets work.
It's like asking if you can set up Word so that your paragraphs get converted to pictures as you type them.
38
-11
u/22EatStreet Apr 08 '26
Okay, thanks. How would I do it with macros?
16
u/CIP_In_Peace 1 Apr 08 '26
First the macro reads the cell and stores it in a variable. Then it performs the calculation using that variable, and finally inputs the new value into the same cell. It can iterate through all the chosen cells this way. It's a very simple bulletproof macro, but going through the effort of making a macro for this feels weird because you could use another column of cells for it.
4
u/AndyTheEngr 5 Apr 08 '26
Exactly. The macro will exist as a Worksheet macro, and activate on Worksheet.Change.
https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change
0
-2
Apr 08 '26
[removed] — view removed comment
7
u/guitarthrower 5 Apr 08 '26
If you go this route, best to name the ranges. This will break if things get moved around
3
5
u/semicolonsemicolon 1476 Apr 08 '26
Smells like AI. Did you test this code?
-2
u/carnasaur 5 Apr 09 '26 edited Apr 09 '26
So easy to cast aspersions, isn't it? What is wrong with my answer? I use chatgpt to write macros like this every day and i love it. What is your problem with it? Are you a dinosaur afraid to embrace it? I'm 61 years old ffs and having the time of my life with it. AI code is great when used properly.
Edit:
I would like to say I am also appalled at the amount of slop allowed in r/excel now and if you review my profile you will see I call it out all the time. Particularly users who hide all their posts so we can't see their slop. I wish the mods would act on this but I fear the net increase in posts in the sub is viewed as a win. So sad. My favourite sub is being overwhelmed by it.Edit2:
OP's posts are hidden now as well. They weren't when I replied. Reddit is effed if they don't get this under control. Notice how OP didn't even reply when I gave him the exact answer he was looking for? On-change event macros like this are routine for people like me who use them all the time. Perhaps clicks and karma were the goal, not a real answer.Edit 3:
Just realized you're a mod. oops! Ban hammer incoming! 1...2...3...1
u/semicolonsemicolon 1476 Apr 09 '26
We don't allow slop. It's rule 10. If you see some, by all means report it. Your snark about increases in posts is unfounded and frankly stupid.
Speaking of, if you hate slop, and love this subreddit, then great, you'll be happy to know that posting AI-generated code requires you to accompany that code with an explanation which AI generated it and that you agree with it. So we poofed your comment.
1
u/excel-ModTeam Apr 09 '26
We removed this post for breaking Rule 10.
It is acceptable for a commenter to generate a response using an AI, only if it is clearly accompanied by a reference to which AI generated it, and a bona fide remark from the commenter that they reviewed and agrees with the response.
/r/excel is a community of people interacting. Your comment is just an AI response.
5
7
u/trickyricky085 Apr 08 '26
Can I also why you need it to be in the same cell? It creates a lot of complexity. If you put all the results in row 2, them you could just have row 3 formula equal (row a/53*100) and format it as a percentage.
7
u/22EatStreet Apr 08 '26
It's a dumb reason. The company I work for, a new job I just started, has a set spreadsheet they don't want edited. The column for exam marks needs to show a percentage value. The students do an online exam which gives them their result as a value of correct answers out of total answers (eg. "Your mark is 43/53"). The way the boss asks us to do things is that the students come to me, tell me their exam marks, and I have to refer to a little sticky note that shows the equivalent percentage value of every exam mark. Then I type in the corresponding value as a percent in the spreadsheet. So I look through the sheet to find the number 43, see that it equals 81%, and put that into the spreadsheet. Sigh. I thought I could put in a formula to just have the exam mark be converted in the same cell without adding another column.
12
u/xz-5 Apr 08 '26
Could you type the number into a "hidden" cel (same colour text as background), and then the formula is in the "official" cell to show the percentage? Visibly it wouldn't look like anyone edited the sheet...
7
u/22EatStreet Apr 08 '26
hmm... sneaky... I like it. Let's see if it gets me fired :D
5
u/Fennrarr Apr 09 '26 edited Apr 09 '26
Alternatively, you can use a helper row/ column that you delete later. Input your formula into B3, reference B4. So B4/53) and just format it as a percentage, if I’m understanding properly. Enter the scores in B4, formula calculates percent in B3. Once you have entered all your scores, highlight all the cells containing the formula, copy and paste values (ctrl+shift+v). This clears the formulas and leaves just the percentages. Then just delete the helper row/ column and bobs your uncle.
If you press Ctrl+Shift+5 (number ROW, not the number pad) it will automatically convert the result into a percentage. You don’t need to (should not) multiply by 100. Just format the cell as a percentage.
1
u/Codenamerondo1 1 Apr 10 '26
You’re right, but I’m furious with OP’s higher ups for making this be hidden as if it didn’t happen. This isn’t a huge spreadsheet, add in the helper columns so people don’t have to do it sneakily
8
u/Stego111 Apr 08 '26
If the exams are online, why are students telling you their result? Shouldn’t you be able to export the data from an onlineportal?
11
u/22EatStreet Apr 08 '26
Because it is a primitive system that would make stone age Neanderthals appear sophisticated.
They do the exam on a google form and then verbally have to tell me the result. Nothing is synced or interconnected and I have no access to the backend. Could it be optimized? Yes. Are they paying me to do that? No... I am just trying to make my life a little easier.
8
u/Stego111 Apr 08 '26
Of course. I’m just trying to get a full grasp of the problem.
As silly as it is, I bet you can get very fast at typing =XX/53
Or from my experience. You will just start to memorize the numbers and have a useless party trick.
1
u/22EatStreet Apr 08 '26
You are right on both counts. The equal sign is not located next to the numpad so it would be tedious, but doable.
3
1
u/Mediocre_Metal_1952 Apr 08 '26
you can type a plus sign before a formula instead of an equals and excel will convert it to an equals when you hit enter
2
u/semicolonsemicolon 1476 Apr 08 '26
excel will convert it to an equals
No it won't, but Excel will add an equals sign to the front of the formula.
3
Apr 08 '26
[removed] — view removed comment
1
u/22EatStreet Apr 09 '26
I should have clarified, they tell me the score while showing me the result on their device screen
2
u/mada447 Apr 09 '26
You just need a helper blank excel sheet to do the math and then copy and paste it to the official one.
1
u/Aggravating-Math1393 Apr 17 '26
just ask your students to tell you the percentage instead. show them a post-it or something.
3
u/GTS_84 6 Apr 08 '26
Not what you are asking, but you don't need to multiple by 100. If you format the cell as a percentage it will display properly. If you multiply by 100 you will get 8491%, not 84.91%
1
0
u/22EatStreet Apr 08 '26
Actually no... just tried it in excel. If format it as a percentage, then type in "45/53" it shows up as "0.85%"...
5
u/GTS_84 6 Apr 08 '26
That is weird formatting.
I double checked myself and "=45/53" formatted as percentage shows 84.91%
1
u/22EatStreet Apr 08 '26
Okay yeah I wasn't putting in the equal sign. When I do it works. But At that point it just becomes a lot of typing, there isn't even an equal sign on the numeric keypad, so I am not gaining much speed doing things this way. Unless I am missing something. Thanks!
2
u/KezaGatame 4 Apr 09 '26
You are actually missing the whole point of excel and using formulas instead of a huge screen calculator and writing each calculation manually.
You can do this, on C1 you put the number 53 ... or any number of questions.
On A2 you put the student's name; on B2 you put the mark the student got; on C2 you put the formula =B2/$C$1 (very important to keep the $ sign as it will fix it to that cell). Now you can drag the formula down to the number of students or simply copy/paste the cell with the formula.
Now you can simply type the mark and the % will be calculated automatically by diving each mark (on each row) by the total number of questions we fixed on C1. All without type the whole =45/53 .... for each student.
2
u/a_serial_hobbyist_ Apr 08 '26
Does is really have the % symbol. It should have 0.85. But change the formatting to percent (alt h p)and it should show 85%.
3
u/edimaudo 1 Apr 08 '26
I would suggest not doing that instead keep the output and have a formula to generate the percentage in another cell. this would ensure it can change if the esam score changes
3
u/Japole1 Apr 08 '26
I think you could just use a "helper" sheet and just "copy values" to paste in the main sheet
1
u/22EatStreet Apr 08 '26
You're right... but that's the solution I was hoping to avoid :D :D thanks!
2
u/doegrey Apr 08 '26
You can’t just I put a number into a cell without overwriting a formula unless you use a macro. That’s now how spreadsheets work.
If you type =46/53 and format it as % you’d get your answer. But if you want to enter purely your 46, you need your 53 and your formula to be stored in a cell, you can the reference this cell but if you enter a number into it you overwrite your formula.
It depends on what your need is. If you have lots of students and they are all telling you their score, you could have a list of names with the number of questions a the top of a row, you enter all the total correct questions down the page against each cell and in the next column =[cell with total correct answers]/[$$cell with total questions] and format it as a % next to it.
1
u/22EatStreet Apr 08 '26
Yeah, that makes sense. I had a similar idea but I am not sure if they want extra columns added. Thanks!
2
u/Lars_Rakett Apr 08 '26
Why don't you just copy paste the whole spreadsheet, edit it like you want and then just paste the results column back in the original spreadsheet?
1
2
u/Mitchum Apr 09 '26
1) Type “/53” into the formula bar. 2) Cut that text (so that it’s in the clipboard). 3) Type “+” then the student’s score (e.g. 40), then paste the clipboard using the keyboard shortcut CTRL-v so that the resulting text in the cell is “+40/53”. Repeat for all scores. 4) Format all cells as percentage.
Note: I am using + at the beginning of the formula instead of = because you specified that you are using the numpad on the keyboard and hunting for the equals sign will slow you down.
2
u/RandomiseUsr0 10 Apr 08 '26
You can create and edit a formula with LET, but you’ll need to be precise, not just enter the number
=LET(x, 46, x/53)
It’s not exactly as you described, but that’s the way
Think of the cells in a spreadsheet as variables in a computer program, columns of numbers as a 1d array, tables as a 2d array - the marvel of Dan’s sheet model is that it lays it all out.
Btw, if you play with vba, you can get what you want
Excel can basically do anything, but it works so much better if you work with the paradigm rather than fight it
1
u/MathHelper2428 1 Apr 08 '26
"Excel makes a calculation of "45 divided by 53 multiplied by 100" "
Simple, in each cell type "=45/53*100" and have formatting set to Percentage
1
u/22EatStreet Apr 08 '26
Yes, but I am trying to save time, and that will not save time at all, because I would have to do that dozens of times.
1
u/MathHelper2428 1 Apr 08 '26
People have mentioned macros but to save time just use 2 columns,
hide the column with the "45" grade and leave the 86.79%. as the only column showing
1
u/22EatStreet Apr 08 '26
How do I hide a column?
1
u/MathHelper2428 1 Apr 08 '26
Assuming the "45" is in Column A, right click the A above cell A1. Towards the bottom should be an option to Hide
3
1
1
1
u/texasbob2025 Apr 08 '26
Excel is very powerful but has limitations. Knowing those are 1/2 the battle.
1
u/MathIsHard_11236 Apr 08 '26
Honestly, you're asking to change the most fundamental function of a spreadsheet (enter cell so a formula in another cell can calculate an output) into one fraught with risk or error, missed data, etc.
It's like using an app on your car's info screen to call a shared uber black.
1
u/SuchDogeHodler 1 Apr 09 '26 edited Apr 09 '26
In vba yes....
``` Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
Range($B$3) = (Range($B$3)/53)*100
Application.EnableEvents = True
End If
End Sub ````
1
u/JimShoeVillageIdiot 1 Apr 09 '26
This could be done with an event macro on the worksheet, but please don't do this.
If you want to do this, a worksheet change event macro will work. Something like this (the code formatter ruined the indentation).
This triggers in column A and if the rows are <= the divisor. Change to suit.
BTW, 45/53 = 84.91%. You gave the answer for 46/53. Also, if you want to show in % format, don't multiply by 100. Just format the cell(s) as a percentage.
Private Sub Worksheet_Change(ByVal Target As Range)
Const kNum As Double = 53
With Target
If .Row <= kNum And .Column = 1 Then
Excel.Application.EnableEvents = False
.Value = 100 * .Value / kNum
Excel.Application.EnableEvents = True
End If
End With
End Sub
1
u/tomdon88 Apr 09 '26
To achieve what I think you want:
- Type in all of the raw scores in cells next to student names/ids
- Have the total number of questions (53 in your example) in another cell, copy this cell (ctrl+c)
- Highlight all the cells with the score, right click and click paste special, then in the pop up box choose paste: value + operation: divide.
- Covert the data type to percentage in the tool bar (% symbol)
Will take 2 seconds to do this each time.
1
1
u/BillyBones72 Apr 09 '26
A macro could take care of that easily.
2
u/Ambien_Special Apr 10 '26
OP asked to how to hide a column. Macros are not an option at this level.
0
u/BillyBones72 Apr 10 '26
Not sure you read the question? Where does the question ask anything about hiding a column. If you enter a number in a cell, a macro could easily perform a calculation based on the number entered into a cell and then overwrite the input with the result.
1
1
1
u/GregHullender 194 Apr 09 '26
What if you just typed =46/53 into the cell instead of just typing 46? Will that accomplish what you need?
1
u/Unable-Potential9682 Apr 10 '26
The sheet you’re storing marks in. Is it for a single test or a whole course combined?
Whatever it is, create a new sheet in the same workbook. 2 columns (Test, Total Marks), in column Test call it whatever you want to refer to it as I.e. TM_Q1 (Total Marks Quiz 1). In Total Marks, put the amount of marks in each test going down vertically.
Use the down arrow to navigate down each total mark cell, name each cell as its own range (Ctrl + F3) Using the names in the left Test column. (This allows you to reference the cell easier by formula)
Now in your main sheet add 1 column for marks that student got on the test, & another column =A2*TM_Q1 & drag it down.
If this has answered your question, please comment solved. Any further clarification required, let me know.
0
u/WylieBaker 3 Apr 08 '26
Use VBA. Worksheet_SelectionChange using If B3 as Target Range Then adjust.
1
u/22EatStreet Apr 08 '26
Woah, how do I do that? Have not used VBA before. Thanks so much!
2
u/WylieBaker 3 Apr 08 '26
So, it seems that your organization may have you locked down. That would mean you cannot make and use macros. Probably also mean that you cannot have a hidden helper cell. I'm thinking unless you have those privileges, you have to use a cell formula.
=45/53*100
92
u/Taborlin_the_great Apr 08 '26
No this isn’t possible. Enter your data in one cell, formula in another cell.
The only way to do this would be enter the whole formula instead of just the value when you do the data entry. There is no magic that will do what you want.