Setting conditions.

[dimlos]

Good day people! I'm kind of in need of help with my 2007 Excel. How do I create a formula for something like this:

If I type 20', 40', reefer, or 20x20' on a cell, how do I set a fixed numeric value for each to the other cell? Like 250 for 20', 450 for 40' and reefer, and 500 for 20x20'?

Sites suggest to use conditional formatting, but I don't know what to do or formula to use.

[etaf]

in the other cell - type an IF() formula
you could also use a look up table

assuming you type in cell A2

in the "other Cell" you want the result put

=IF( A2= "20'" , 250 , IF( AND( A2="40'", A2="reefer") , 450, IF( A2="20x20'" , 500, "criteria not in cell A2" )))

if that does not work - then

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[dimlos]

in the other cell - type an IF() formula
you could also use a look up table

assuming you type in cell A2

in the "other Cell" you want the result put

=IF( A2= "20'" , 250 , IF( AND( A2="40'", A2="reefer") , 450, IF( A2="20x20'" , 500, "criteria not in cell A2" )))

Thank you very much! I removed the "AND" part because it kept on giving me a "FALSE" value. I replaced it with another "IF", and it worked. I'm kind of confused about the "criteria not in cell A2" though. If I delete it from the formula, it'll give me a "FALSE" value if for example "A2" is empty. I just replaced it with "(empty space)" so that the other cell will look empty.

=IF(F3="20'",250,IF(F3="40'",450,IF(F3="20x20'",500,IF(F3="reefer",450," "))))

Again, thank you for helping a beginner.

[etaf]

Sorry - that should have been an OR
not an AND - my mistake

that " "
is actually putting a space into the cell
use
""
which will leave the cell blank

so with an OR
its
=IF( A2= "20'" , 250 , IF( OR ( A2="40'", A2="reefer") , 450, IF( A2="20x20'" , 500, "" )))

But another IF is fine

I'm kind of confused about the "criteria not in cell A2" though. If I delete it from the formula, it'll give me a "FALSE" value if for example "A2" is empty.

Yes if none of those criteria exist - then you need to tell excel what to do if false
hence if you leave the part missing you get a FALSE
otherwise a " " or as i suggested "" will leave the cell blank