Solved

Excel Formula

Posted on 2014-11-16
13
40 Views
Last Modified: 2015-05-02
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
0
Comment
Question by:ramoghaddam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40446463
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.
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 40446538
where are those values coming from? 77.8? 17?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40447641
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
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 

Author Comment

by:ramoghaddam
ID: 40454301
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40456774
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
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 500 total points
ID: 40456789
In the same line of thought of the first file posted this one is a little bit upgraded as the function is Called Coef and it accept 2 variable the first one the grade and the second one the Coefficient so you decide where and what items you want to affect what coefficient by simply putting the appropriate figure. In the previous example you will see in Cell B3  =Coef(78.8,17)
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
0
 

Author Comment

by:ramoghaddam
ID: 40461184
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?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40461550
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
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40461554
Also we can do what you want in an other sheet keeping the original one untouched is this an option for you ?
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
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 500 total points
ID: 40462428
ok I guess here is what you want.

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
0
 

Author Comment

by:ramoghaddam
ID: 40475071
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40475287
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
0
 

Author Comment

by:ramoghaddam
ID: 40756679
I've requested that this question be deleted for the following reason:

None of the answers were helpful.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question