Excel Formula
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
Does the value 77.8 come from a cell in this worksheet?
Something like this...
=(A1/100)*17
...where cell A1 contains the value 77.8.
Something like this...
=(A1/100)*17
...where cell A1 contains the value 77.8.
Your issue is easy and not that easy at the same time. The problem lies in the fact that it seems that you do not want to have the data in a place and the formula in an other place. you want to replace the existing amounts by a formula but this will still generate work for you.
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
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
Okay so here is the deal. The 77.8 doesn't come form another cell or worksheet. Basically it's the % student got on that assignment; I have to convert that to a decimal % (dividing by 100) and then multiply it by the points that the assignment is worth 17. This provides what the student got out of 17 points. My problem is that I have to do this for the entire worksheet and the point vary based on the assignment. So columns S-AG have assignments worth 17 of which each student has earn a percentage which I have to calculate manually by the process I described above. Appreciate your efforts.
Ramy
Ramy
Sorry too get back late as was sick the couple days.
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
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
I follow the logic, however that would still require me to input the % for each cell (78.8, 93...) and that is what would be cumbersome to say the least for so many entries. Looking for a way to have the formula multiply the changing % figure for each item and keep the conversion to % (divide by 100) and points for that assignment (17) static. Is that possible?
I do not follow you !
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
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
That looks to be the proper solution but how can I set that up? I will need download the updated worksheet and plug in the formulas to calculate final grades. basically each block of assignment is associated with different point values (cells S2-AG2 = 17, AH-AI = 52...). So I need to know how to set up the formula so that when my worksheet changes I can adjust it accordingly. I think it would work out better if we talked over the phone if that works for you. Text me and we will set something up if you are up to it. My number is 559-250-8426.
Ramy
Ramy
Sorry we can only communicate thru EE as this is 'break policy' if violated.
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
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
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.