Word Controlled Multipliers

[Takron]

I am making a gold and silver investment workbook and since gold comes in different karats, I want to have a karat column for my multiplier. I want to be able to put my grams in the gram box and then when I put 14k or 18k in my karat box it recognizes it and multiplies the information by the required percent based on what karat I put in the box.

Grams Ounces Price Karat
[106 ][ 3.74 ][ xyz][ 18k]

as opposed to

Grams Ounces Price Karat
[106 ][ 3.74 ][ xx ][ 10k]

And I want it to recognize the ''XX.K'' command to change the price percentage automatic enstead of having to change the percent manually every time. So if I update the karate with a different one say change from 10k to 18k or 14k it would recognize that and change the percent for me.

 

[SydneyGeek]

Use VLOOKUP.
You can create a table like...

Excel Workbook
	P	Q
3	9K	37.5%
4	10K	41.7%
5	14K	58.5%
6	18K	75.0%
7	22K	91.6%
8	24K	100.0%
Sheet1

Then refer to it in a formula like this (assuming that you type the karat into D3) --
=VLOOKUP(D3,$P$3:$Q$8,2,False)

This will search the first column of the table (ie, P3:P8), and when the match is found, return the value in the second column. You can then multiply that by the ounce column to get the amount of gold in the item.

Denis

 

[jdmc45]

To recognize the number of carrots in a string of 18k (assume it is in D1) for example, use

=MID(D1,1,2)

This will return 18. Then you can use an IF statement for the percentages based on the number of carrots. If the percentage for 18k is 50% then use nested if functions to do the rest of the percentages and fill down. So assuming A1 is the number of grams

=IF(MID(D1,1,2)=18,0.50*A1,IF(...))

The nested if function could be quite long, but won't take long to do and is easily filled down.

Hope that helps