creating a function in excel...having trouble

1168 Views 1 Reply 2 Participants Last post by Dragoen, Mar 30, 2011

Nihilistiskism

Discussion Starter · Mar 30, 2011

Office 2010:

Need to create a function with multiple possible variables to create a sum total for a row.

Example:

If Cell B2 contains text "dragon" add 40 to sum, if Cell B2 contains text "tiger" add 60 to sum, if Cell B2 contains text "Adult" add 75 to sum. How to write this as a function?

I know that =IF(B2="dragon",40) will get the function to add 40 if "dragon" is entered in that cell, and do nothing otherwise, and I know that =IF(B2="dragon",40,60) will get the function to add 40 if "dragon" is entered in that cell, and add 60 otherwise, but I can't figure out how to program that third variable. Ideally, it would be able to put the correct amount in based on which of the three text options is entered into the cell.

Help?

Save

Status: Not open for further replies.

1 - 2 of 2 Posts

Dragoen

· #2 · Mar 30, 2011

Hi Nil,

If you don't have a lot of options to code for you can nest the IF statements like:

=IF(B2="Dragon",40,IF(B2="Tiger",60,IF(B2="Adult",80,)))

If you have many more IF's to nest then I would recommend making a look up table and using a VLookup function or Index & Match.

With the Vlookup you would create a range of cells containing the field values (Dragon, Tiger, Adult) down one column and in the next column to the right put the values 40, 60, 80.

hth

See less See more

Save

1 - 2 of 2 Posts

Status: Not open for further replies.

Top Contributors this Month