 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Newbie question / plug in a value for a cell based on a range for that value
Hey guys, New to Excel so sorry if this explanation isn't the clearest in terms of correct terminology. In one section of my spreadsheet I have a list of percentage values associated with numbers 1-20. For example #10 is listed with the percentage value of 38.4% in the cell next to it (O14 and P14). In another section of the spreadsheet I have a quick formula that I'd like to use these %'s based on what number (1-20) is entered. In my spreadsheet the % I want to use for this #10 is listed in P14 (38.4%). What can I plug into my formula so when I enter "10" is knows to use 38.4% to use for calculations? Not sure if that was clear enough so let me know try to give a simplified calculation as that might help explain what I'm trying to do: In E5 I have the formula =(C5*D5) In C5 I enter "10". In D5 the value entered is $100. I'd like for the spreadsheet to automatically multiply the 38.4% by $100. So the result in E5 Should be $38.40. So, what can I enter into my formula to use the % associated with the number I enter? Assuming the values in your table occupy the cells O5:P24, then use this formula in E5: =VLOOKUP(C5,O$5:P$24,2)*D5 instead of C5*D5. Hope this helps. Pete > Hey guys, > [quoted text clipped - 23 lines] > So, what can I enter into my formula to use the % associated with the number > I enter? Thanks Pete, My example was a simplified one. I thought it might be an easy formula I could enter to find the associated %. Then I could use that to plug into my specific formula. If I can find out how to enter a number 1-20 in a cell and have the associated % added into a different cell I could then figure everything else out. Is there any way to add a simple formula into another cell where it will grab the percentage associate with the number 1-20 from the range I have listed in my worksheet? So if I enter in C5 the number 10, what formula can I enter into E5 so that E5 will be 38.4%, which is the % associated with the number 10? And yes, you are correct that the numbers 1-20 and the associated percentages are found within my worksheet at O5:P24. THANKS AGAIN PETE Assuming the values in your table occupy the cells O5:P24, then use this formula in E5: =VLOOKUP(C5,O$5:P$24,2)*D5 instead of C5*D5. Hope this helps. Pete On Mar 24, 10:50 pm, "FISH" <n...@spam.com> wrote: > Hey guys, > [quoted text clipped - 27 lines] > number > I enter? You just need the first part of the formula that I gave you, i.e.: =VLOOKUP(C5,O$5:P$24,2) Your numbers 1 to 20 need to be in sequence in O5:O24, with the corresponding %age values in P5:P24, and then this formula (in E5) will give you the %age which corresponds to the number you type in C5. If you enter 10.1, for example, it will still give you the %age which corresponds to the value 10. Hope this helps. Pete > Thanks Pete, > [quoted text clipped - 64 lines] > > - Show quoted text - Perfect, thank you very much. You just need the first part of the formula that I gave you, i.e.: =VLOOKUP(C5,O$5:P$24,2) Your numbers 1 to 20 need to be in sequence in O5:O24, with the corresponding %age values in P5:P24, and then this formula (in E5) will give you the %age which corresponds to the number you type in C5. If you enter 10.1, for example, it will still give you the %age which corresponds to the value 10. Hope this helps. Pete On Mar 24, 11:51 pm, "FISH" <n...@spam.com> wrote: > Thanks Pete, > [quoted text clipped - 72 lines] > > - Show quoted text - You're welcome - thanks for feeding back. Pete > Perfect, thank you very much. > [quoted text clipped - 92 lines] > > - Show quoted text - One more related question on this; I actually have two sets of percentages associated with the numbers 1-20. In one situation it would be one set of percentages and in another situation it would be the next set of percentages. So in O5-O24 I have the numbers 1-20. In P5-P24 I have the first set of percentages. Then in Q5-Q24 I have the second set of percentages. By adding =VLOOKUP(C5,O$5:P$24,2) into E5 for my first calculation it works great. When I enter a number 1-20 into C5 it automatically enters the associated percentage in E5 found in the P column. I have a second calculation that uses the percentages found in the Q column. What can I enter into the cell E10 so that when I enter a number from 1-20 in C10 it will automatically enter the associate percentage found in the Q column? So for this example the associated percentage for 10 (still found at O14) would be found in cell Q14 (which is 21.7%). Thanks again Pete for the help. You're welcome - thanks for feeding back. Pete On Mar 25, 1:51 am, "FISH" <n...@spam.com> wrote: > Perfect, thank you very much. > [quoted text clipped - 96 lines] > > - Show quoted text - VLOOKUP is a very flexible formula - the fist parameter is the value that you are trying to find in the left-most column of the table which is the second parameter. The third parameter determines which column from the table that a value is returned when a match is found. (There is a fourth optional parameter which enables you to specify that you want an exact match). In the previous formula your table was only two columns wide, so the column value could only be set to 2. In the case you have just described, however, your table is now 3 columns wide and you want to get data from that third column. So, you need to amend the formula like so: =VLOOKUP(C10,O$5:Q$24,3) and put this in E10. Hope this helps. Pete > One more related question on this; > [quoted text clipped - 127 lines] > > - Show quoted text - Got it, thanks again. VLOOKUP is a very flexible formula - the fist parameter is the value that you are trying to find in the left-most column of the table which is the second parameter. The third parameter determines which column from the table that a value is returned when a match is found. (There is a fourth optional parameter which enables you to specify that you want an exact match). In the previous formula your table was only two columns wide, so the column value could only be set to 2. In the case you have just described, however, your table is now 3 columns wide and you want to get data from that third column. So, you need to amend the formula like so: =VLOOKUP(C10,O$5:Q$24,3) and put this in E10. Hope this helps. Pete On Mar 25, 5:03 pm, "FISH" <n...@spam.com> wrote: > One more related question on this; > [quoted text clipped - 142 lines] > > - Show quoted text - You're welcome - thanks for feeding back. Pete > Got it, thanks again. > [quoted text clipped - 167 lines] > > - Show quoted text - Excel does not have spreadsheets. It has worksheets. Perhaps a good book such as one written by John Walkenbach would help. Just trying to help you with terminology and understanding. I always find it helpful to read a good book about something that is new to me. Tyro > Hey guys, > [quoted text clipped - 23 lines] > So, what can I enter into my formula to use the % associated with the > number I enter?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.