Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Trying to create a formula that will take the value in one cell turn it into a % and multiply it by a static number. I need to then copy the formula to multiple cells so that I would not have to carry out the manual calculation over and over. I have attached the spread sheet for more detail. Look at the formula in cell S2. The 77.8 convert to % and multiply by 17 which is the static value. So I need the formula to keep the % conversion and multiplication to 17 static while it adjusts to the value that changes in other cells (the 77.8). Thank you.

Ramy

Grades.xls

Ramy

Grades.xls

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

Something like this...

=(A1/100)*17

...where cell A1 contains the value 77.8.

Anyway what I did is a volitale function that will re-calculate each time the Excel calculate like each time anything is change in the worksheet this formula will re-calclulate. It is in the module.

You put your static amount 17 in this case in cell C1 and then in each and ever cell where you have data like the example I put in Cell B3 you convert what ever Value you have by the following:

Say your cell S3 contain 93 you will then put:

=fstat(93)

and press enter you will get 15.81

Now you do this for all the grades that you have once and for all.

When your done simply change the 17 in Cell C1 to something else and you will get your new conversion. To get back your original amount you put 100

Is this what you want ?

gowflow

Grades.xlsm

First did you have a chance to check the file I posted ?

If yes the is the proposal agreeable to you this way ? (despite the fact that you would want a different ratio for different columns this could be arranged but the principle for you to convert every cell putting the formula is ok ?

For sure I could offer plenty of other solutions that would not involve manipulation from your part but you had asked something specific to which I answered specifically.

gowflow

B4 =Coef(93,17)

and again for me this is not an optimal solution but if your happy with it I have no problem.

gowflow

Grades-V01.xlsm

As you are assuming I know what are your steps of work. Please explain in detail what you want and how now you do it then it would be easier for me to get you a solution.

gowflow

Like you would hv your main sheet that hv the perc and an other one having what you desire.

If yes then you need to tell me the mechanics ... like for me it is not clear so far who decides on the 17 isn't it you ? and if yes then it goes for what columns these are 2 variables that need to be arranged and after then it is piece of cake.

My suggestion would be to add row1 and call it coefficient where under each columns you would put a figure Like Col A to M would have 17 in each column at row1 Col N to Z would have 30 etc...

if this is an option for you then easy we create a small macro that give you the result in an other sheet each time you hit a button we call CALCULATE.

What do you think ?

gowflow

This version of the file has 2 sheets

Grades: is original sheet where you will input all your data in percentages

Grades w Coef: is the sheet produced by the macro that will give you final result.

Basically in your sheet Grades you noticed I added in Row1 that is labeled Coeficient where on top of every essay I put a figure these are for testing purposes.

You have a button COMPUTE each time you activate it, it will delete the existing 'Grades w Coef' then copy the entire Grade Sheet and paste it in a new sheet called 'Grades w Coef' and will perform the Coeficient calculations as you explained.

Please check it and let me know your comments.

gowflow

Grades-V02.xlsm

To make it clear there is NO FORMULAS to do in the latest version I posted. The only thing you need to do is to have on Row1 in the same format that I posted the point values input for each column, So you need to put 17 in every column S, T, U, V W .... till AG

and 52 for AH, AI

Let me know if it is not clear.

gowflow

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial