I'm sure this is simple...but doesn't work

[tralala]

Hi, I'm building up a record discography and I'm setting prices for each record but i'd like to make it easier to convert the price of each according to its condition compared to a mint condition price. Here's an example:

column M is the price of a perfect condition record (for example 20$)

column H is the condition of the record and is used to set the end value of my record
EX = 100% of the M column
VG+ = 50% of the M column
VG = 25% of the M column
G = 12.5% of the M column
F = 6% of the M column

column I is the condition of the cover and is used to set the end value of my record
EX = 100% of the M column
VG+ = 50% of the M column
VG = 25% of the M column
G = 12.5% of the M column
F = 6% of the M column

column K (most important of all) is the current value of the record according to the columns H and I. This should be the sum of the result of column H and I gradings divided by 2.

Here's a manual example:

Record #1 (mint copy is worth 20$)
mine is VG+ (cover) and VG+ (record)
so the value of my record is 10$.

I'm sure this isn't difficult to achieve, but i can't get it working.

Thanks for your help.....


Daniel Allard

[Mallycat]

Correct, not difficult, but easier if you post your wip spreadsheet rather than start from scratch.

[protonLeah]

Say you have an unused column, "P"

1. Put the values of the multipliers in this column:
   1
   .5
   .25
   .125
   .06
2. Select the cell with the "1" value and, in the NAME box on the formula bar, type EX. Select each of the remaining cells, naming them VGP, VG, G & F.
3. Select the first cell of H or I column. From the menubar, Data>Validation>Allow:List,
   in the SOURCE box, type the catagories EX,VGP,VG,G,F. Check the In-cell dropdown option and uncheck the ignore blank option.
4. Copy your data validation criteria down through the used rows of cols H & I.
5. Select column K and enter the formula:
   =(INDIRECT(H2) + INDIRECT(I2)) * M2/2, and copy down.

[clayv]

I think this will do it for you (assuming data starts in row 1):

=(HLOOKUP(H1,{"EX","F","G","VG","VG+";0.5,0.03,0.0625,0.125,0.25},2)+HLOOKUP(I1,{"EX","F","G","VG","VG+";0.5,0.03,0.0625,0.125,0.25},2))*M1

- Clay Ver Valen

[tralala]

Thanks for answering, i've tried the two suggestions posted the fisrt one (protonLeah) worked but is there a way of using VG+ instead of VGP. Well, here's a 3 lines copy of my worksheet if you can solve the problem...

Thanks in advance, you're too nice

[Mallycat]

Here is what you are after

[tralala]

THANKS A LOTTTTTTTTTTTTTTTT, my life is gonna be quite easier now