If then statement

D

dl3r

Hi,
I'm having trouble creating an if then statement. I have a comic boo
collection that I want in an excel format so that it update
automatically.

number___condition___guide____NM____VF+
...300...........VF+........4.50........5.......4.50

As you can see I want the condition to be linked to the NM, VF+ so tha
if I change the condition, the guide price will change. I know I hav
to imput all the prices first, but once that's done, I want it so tha
if I change the condition of the book, it'll auctomatically change th
guide price.

Thanks in advance.

dl3
 
N

Norman Harker

Hi dlr3!

We details of the relationship between guide price and NM / VF+

However, it looks like a VLOOKUP problem with %age adjustment based
upon condition vs.. VF+ price.
 
R

RagDyer

I understand that a changing condition will change the price.

What I don't understand is in your example, you showed 5 columns, where the
last column header is a condition (VF+), with a price under it, while you
have the same price in the "guide" column!

Could you try describing your list more clearly?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


Hi,
I'm having trouble creating an if then statement. I have a comic book
collection that I want in an excel format so that it updates
automatically.

number___condition___guide____NM____VF+
...300...........VF+........4.50........5.......4.50

As you can see I want the condition to be linked to the NM, VF+ so that
if I change the condition, the guide price will change. I know I have
to imput all the prices first, but once that's done, I want it so that
if I change the condition of the book, it'll auctomatically change the
guide price.

Thanks in advance.

dl3r
 
D

dl3r

What I'm attempting to show, is that the NM and the VF+ is a column i
the guide price. It only changes once a year, so I will physically pu
in the numbers in the spread sheet. The guide column is the price o
the book that I own, meaning it's the guide value of the book that
have in the condition column.

For instance, when you see condition, that column represents th
condition that I currently have. The guide column will represent th
price of the book based on the condition of the book. The last column
being NM and VF+ are constants given by the comic book price guide.

If you see my jpeg, you'll see how I want to connect things. Pleas
let me know if this helps. Thanks so much

Attachment filename: untitled-1.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=56727
 
R

RagDyer

I wouldn't open your attatchment (or anyone elses), but I think I have a
general idea of what you're looking for.

Your list will be only three columns:
A = I.D.Number
B = Condition
C = Present Value (price)

The Comic Book Price Guide List will be out of the way.
Say you use columns Y and Z.

Column Y will be the "Condition" and column Z will be the "GuidePrice".
Say there are five existing conditions and their associated prices.
So the CBPGL will have data in Y2 to Z6.

With your list using headers in row 1, your first book I.D. Number is
entered starting in A2.
In B2 you enter the condition for this book.
In C2 enter this formula to get the value (price) from the CBPGL that
matches the condition in column B:

=VLOOKUP(B2,$Y$2:$Z$6,2,0)
Drag down to copy as needed.

This will give you a #N/A error if there is no condition entered in column
B, OR, the condition entered in column B does *not* match anything in column
Y.

If you might prefer to to have an empty return instead of the error message,
you could use this formula:

=IF(ISNA(MATCH(B2,$Y$2:$Y$6,0)),"",VLOOKUP(B2,$Y$2:$Z$6,2,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


What I'm attempting to show, is that the NM and the VF+ is a column in
the guide price. It only changes once a year, so I will physically put
in the numbers in the spread sheet. The guide column is the price of
the book that I own, meaning it's the guide value of the book that I
have in the condition column.

For instance, when you see condition, that column represents the
condition that I currently have. The guide column will represent the
price of the book based on the condition of the book. The last columns
being NM and VF+ are constants given by the comic book price guide.

If you see my jpeg, you'll see how I want to connect things. Please
let me know if this helps. Thanks so much.

Attachment filename: untitled-1.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=567273
 
D

dl3r

Thanks, however when you look at my columns, I have them goin
horizontally across with the stated conditions as in the guide book.
understand that you don't want to download my jpeg, but it makes i
very clear how I'm attempting to organize this. Will you accept a
e-mail from me? Basically, the formula would work, however, I do no
have the price guide columns vertically, I have them horizontall
starting with j1 to u1 having the conditions, j1 = nm, k1 = vf/nm, L1
vf+ and so on. Below that I have another line which states the numbe
associated with the condition, hence j2 = 9.2, k1 = 9.0 etc. Belo
that I have the prices in the guide, j4 = 1200, k4 = 894, etc. Pleas
let me know if I can accomplish what I'm doing going horizontally?

The reason for that is on b4 I have the comic book number, c4 I hav
the title, d4 I have the condition, e4 I have the price sold on ebay
f4 I have the guide price of the condition of the book that I own whic
should be connected to d4, g4 is the price i paid for the book. J4 i
what is above this paragraph. Thanks for the help
 
C

Charlie O'Neill

I am not sure if I understand your problem but see if a HLOOKUP in F4 will
work.

=HLOOKUP(D4,J1:U5,4,FALSE)

This will lookup the condition of the book in D4 and find the corresponding
price in table J1:U5 row 4. For this to work the entry in D4 must match
exactly one of the headings in J1:U1.

Charlie O'Neill
 
R

RagDyer

J1 is the condition row.
J2 doesn't concern me, right?
J3 is missing!
J4 is the price you want to be returned.

You will manually enter the condition for your books starting in D5.
The e-bay price starting in E5 doesn't concern me, right?
The price (value) will be returned to column F, starting in row 5.
Enter this formula in F5:

=HLOOKUP(D5,$J$1:$U$4,4,0)

Drag down to copy as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks, however when you look at my columns, I have them going
horizontally across with the stated conditions as in the guide book. I
understand that you don't want to download my jpeg, but it makes it
very clear how I'm attempting to organize this. Will you accept an
e-mail from me? Basically, the formula would work, however, I do not
have the price guide columns vertically, I have them horizontally
starting with j1 to u1 having the conditions, j1 = nm, k1 = vf/nm, L1 =
vf+ and so on. Below that I have another line which states the number
associated with the condition, hence j2 = 9.2, k1 = 9.0 etc. Below
that I have the prices in the guide, j4 = 1200, k4 = 894, etc. Please
let me know if I can accomplish what I'm doing going horizontally?

The reason for that is on b4 I have the comic book number, c4 I have
the title, d4 I have the condition, e4 I have the price sold on ebay,
f4 I have the guide price of the condition of the book that I own which
should be connected to d4, g4 is the price i paid for the book. J4 is
what is above this paragraph. Thanks for the help.
 
M

mark

-----Original Message-----
Hi,
I'm having trouble creating an if then statement. I have a comic book
collection that I want in an excel format so that it updates
automatically.

number___condition___guide____NM____VF+
...300...........VF+........4.50........5.......4.50

As you can see I want the condition to be linked to the NM, VF+ so that
if I change the condition, the guide price will change. I know I have
to imput all the prices first, but once that's done, I want it so that
if I change the condition of the book, it'll auctomatically change the
guide price.

Thanks in advance.

dl3r
Click to expand...
second two rows show the formula without the "=" sign in
from of the IF so you can see what the formula looks
like. E32 is the condition column, H32 is where the VF
price is, G32 is where the NM price is.) The data didn't
copy very well into the email response, so be sure to line
up the columns. The 5.00 and 4.50 are wrapped to a
second line but are actually the values in the nm and vf
columns. Good luck.

number condition guide nm vf
300 vf 4.50 5.00 4.50
300 nm 5.00 5.00 4.50

300 vf IF(E32="vf",H32,(IF(E32="nm",G32)))
5.00 4.50
300 nm IF(E33="vf",H33,(IF(E33="nm",G33)))
5.00 4.50
 
Top