Calculator in Excel

[Guest]

Hello,

I've read through some of the posts to find an answer to my problem but
without much luck. Essentially, what I'm trying to do is create a calculator
that will help me determine pricing on some items. Below is an example of
the two source columns I'm interested in using:

Fruit Price
Apple $0.10
Orange $0.20
Pear $0.30

Now I've created a drop-down list - using data validation - in one cell from
which I can select a fruit. What I'd like is for Excel to return the value
of the fruit in the cell adjacent to my selection. So, for example, if I
select "Pear" I want $0.30 to appear in the cell immediately to the right of
my selection from the drop-down list.

To further complicate the issue, I'd like to be able to add a new value (in
this case price of fruit in $cents) and have Excel return what percent above
my initial price that is. So to continue the example from above, my end
result would look something like this:

Fruit Unit Price New Price Percent Above Unit Price
Pear $0.30 $0.40 133%


I think I'm supposed to use the VLOOKUP function on a pivot chart but am
still struggling with that.

Thanks for taking the time to read my post. I realize it's long, but I
wanted to be as clear about my problem as possible. Any help would be
greatly appreciated.

Thanks

 

[Guest]

Taking your question in 2 parts. The first is displaying the price of an item
from a list of items and prices, you are correct about using Vlookup and this
assumes you items are in Col A and prices are in Col B.

=VLOOKUP(D1,A1:B100,2,FALSE)

Entering the item name in Cell D1 will return the price.

For increase in price in another cell simply multiply by (say) 1.33 using

=B1*1.33 which gives the new price. You can now copy and paste this over the
original price. Note that this will reurn an error if you have simply put the
$ sign in front of your price. To get the dollar sign you must format the
cell as currency.

Mike

 

[Guest]

Mike,

That worked wonderfully well, thanks!. My whole mistake was I was entering
a 4 instead of 2 (the 2 being the number in your formula below between B100
and FALSE) because I thought that part of the formula was in which column -
in my case the fourth column - I want the result to be returned, not the
number that indicates what column to search for the value of price.

Once again, thank you for your time Sir.