Is this possible?

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
I have a sheet of grades in columns. The grades are "raw scores" and don't reflect the real grade since the assignment may have only been worth 80 points. So the 72 in that column is actually a 90%. I want to have a menu option that allows an immediate change of all grades to their percent score, and another that changes them back to their raw scores. I can do this by pasting formulas in that take the raw score from another area where they were copied when the menu selection is activated, divide it by the maximum points possible and multiply it by 100, but then I can't add or change any grades without first copying the raw scores back over the formulas ... Is it possible to have formulas "in the background" that allow input into the same cell? I have seen a program that does this. The user can actually put in raw scores and they instantly change to the percent score if that is the selected view. But when a cell is selected, it shows the raw score, and may be changed, until it is not the active cell, where it changes back to the percent. Is that possible with Excel?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
Thanks, I'll look into it. I was pretty amazed when I saw it happen in another data base-stand alone program, but I'm not sure that it originated with Excel though everything about it was very "Excelish."
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Why not simply create another column that has the percents? If you want to "flip back and forth" you could simply put the auxiliary column off to the right far enough that it’s not on screen at the same time and then drop a hyperlink into the header to jump you back and forth. This would be a codeless solution.

Otherwise, create a button (or custom menu option if using Excel 2003 or lower) that will hide/unhide as a toggle. You could get most of the code needed from the macro recorder. This does drag macro security into the mix if you’re going to share the file with others.

I make these suggestions because I am always leery of solutions that overwrite original data. You never want to run the risk of having something go wrong and you end up with corrupted data from which you may not be able to recover. If you are new to coding you need to ALWAYS save a copy of your workbook as a backup before trying new code. (If you're not new to coding then you already do this because you've burnt yourself more times than you want to remember.)

BTW, you don’t say which version of Excel you’re using and that usually helps folks help you.
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
Not as such, a cell with a formula cannot hold a static value. And as Greg says overwriting isn't usually a good idea.

How about making 2 identical sheets, one with the raw data the other with formulas linking to it. So in sheet "percentages" you put e.g. =(Sheet2!D7/80)*100 in D7. And in Sheet2 cell D7 put the score. Then you can "flip" between the sheets. One has raw scores the other percentages.
 

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
Thanks Greg and Snowblitz. I'll play around with your ideas on this. I am writing my own code and have been working on it about 3 months. I'm creating an "on line" grade book for the school I work for. I'm also doing it to learn VBA and improve my Excel knowledge, and it's been incredibly instructive. Thanks for the pointers.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top