Formula for Microsoft Excel?

Question

Here is my problem. I have a list of test results marked out of one hundred.

They have to be reduced to a score out of forty, i.e. seventyfive percent would be thirty.

I would like to be able to just type the percentage into the cell and have it display the score out of fourty.

I tried a few formulas, but it said that it could not do self-references formulas. Is there a way around this?

Additional Details

Hmm, my boss says that so the clients don't get confused, the percentage cannot be shown anywhere on the grid. This means that I can only use ONE box. This is crazy!

Answer 1

u r question is quite confusing but i tried to answer it hope its helpful for u

if u get 80 upon 100 then out of 40 it would be (40*100)/80

if not satisfied with the answer reframe u r question and hope for an exact answer

Answer 2

There's a simple thing in Excel formulas: The result of a formula is displayed in the same cell where you put the formula. Formulas generally (not necessarily) refer to some other cells for this task. And Excel allows only single entry per cell, whether it's a value, a formula or a text.

So simply, you cant put a formula and again use the same cell to enter your 'marks'. (forgetting all about circular reference)

According to your requirement I see 3 possible directions for the solution:

1. Custom number formatting: (Format > Cells... > Number)
You can specify a custom number format to display something different to the actual value in the cell. (Eg: When you enter a number; a date is displayed...)
So if you can specify to display: value*40/100, that is the perfect solution. Unfortunately I don't know how to use formulas there and don't think it is allowed either.

2. If you know about macros, you can tell: when you enter a value into the cell, evaluate it and put the result back into the cell.
But the problem here is, it changes the actual value in the cell to the value you need to be displayed.

3. So forgetting both above, I suggest to try something simple like enter the value into some other column and explain the situation to your boss. Good luck!

Answer 3

Say you want the score out of 40 to be shown in cell A5, and the table that will be printed out only goes out to column G, use a column out of this range to type in the percentage, like column J. When you print, make sure that the print range doesn't include column J.

Silimarly, column J, or any column that you want to type in anything that's not to be printed can be hidden. Just right click on the column heading (the grey thing at the top of the cells bit of the screen that has A, B, C or whatever as column name), and select hide column. You can unhide the column later on by selecting the columns, right click and select unhide.

The formula is as before. In this case, for the score out of 40 to appear in cell A5 and the precentage is located in cell J5, type in cell A5 the formula +J5*40 .

Answer 4

I think that you tried to use the same cell for input and output and that is the reason for self-references error.
You must use two cells. First it is the cell for input data (lets say A1) and the second is the cell for the output data (B1). In B1 use the formula "=A1*40/100" and then go with the mouse pointer in the lower-rigth corner of the cell (where the mouse pointer changes into a small cross - not in a big cross) and drag it down to copy this formula (to B2, B3 .... Bn).
Hence you will have the A1.. An for input and B2...Bn for output.

I hope it will be useful for you.

Answer 5

You cannot apply a formula to the cell you are taking the data from - as this creates a continuous loop. Instead, you will have to do the formula in the column next to it and then you can hide the original column.
The formula you need is:
+(B6/100)*40
Or just:
+B6*0.4

Answer 6

Hi

If I have understood you correctly - you have list of Marks for 100 and you want to convert them to 40 ? and you dont want ot use another column - right

If you can use another sheet what you can do is

(Sheet1!C4)*40%

where sheet1 is where you have you marks for 100 and the cell C4 is the cell where you have the marks
using this formula you can have the marks for 40 in another sheet

hope i am clear

Answer 7

I am not well up on excell but surely if you divide all the reults by 100 then multiply by 40 as I say not too sure about the formula but hope my answer helps....