Tech Support Forum banner

creating a function in excel...having trouble

1168 Views 1 Reply 2 Participants Last post by  Dragoen
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?
Status
Not open for further replies.
1 - 2 of 2 Posts
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
1 - 2 of 2 Posts
Status
Not open for further replies.
Top