Newbie question / plug in a value for a cell based on a range for that value

  • Thread starter Thread starter FISH
  • Start date Start date
F

FISH

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
 
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
 
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
 
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
 
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
 
You're welcome - thanks for feeding back.

Pete

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













- Show quoted text -
Click to expand...
 
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
 
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
 
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
 
You're welcome - thanks for feeding back.

Pete

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












- Show quoted text -
Click to expand...
 
Back
Top