Formula to calculate in one cell based on word in a different cell

**Squint** · 03-23-2010, 09:11 AM

I used to have a formula that would calculate and amount in on cell based on a word in another cell. For example I am trying to set up a registration form and attendees will be paying based on the days they attend. So if I type the word First Day in cell B4 then C4 would automatically display $40. In this case I have 3 choices and I used to have a formula that would use 3 different columns and have 3 different formulas. So if B4 said First Day C4 would say 40, If B4 said second day then D4 would say 70 and if B$ said Both then E4 would say 110.

I also had a spreadsheet at 1 time that had 1 formula for all 3 choices. So it would be if B4 equals First Day C4 equals 40, if B$ equals Second Day C4 equals 70, if B4 equals both c4 equals 100

I can not remember any of these formulas because I have not been using excel as much as I used to for awhile.

I will attach a small sample of what I mean and if anyone is able to help with either choice I would appreciate any help.

Thanks so much!

**NBVC** · 03-23-2010, 10:03 AM

If you have solved this yourself, kindly post your solution in order to benefit future users with similar problem...

Thanks.

**Squint** · 03-23-2010, 11:11 AM

That was an accident on my part. I do not have this solved. I think I have fixed it now

**Squint** · 03-23-2010, 12:02 PM

I did not mean to post this as solved. Do you know how I fix this or should it be deleted and reposted? I am not sure if I am able to delete my own posts. Are you able to help me with this?

Thanks and sorry for the confusion

**ChemistB** · 03-23-2010, 12:15 PM

There are a number of ways to do this. Here's one. In C4 dragged down
=LOOKUP(B4,{"Day 1","Day 2","Day 3"},{40,70,110})
Does that work for you?

**Squint** · 03-23-2010, 12:24 PM

This works great! The only problem is that until I type in Day 1 or Day 2 or Day 3 there is a #N/A showing in the formula cells. Do you know how I can get rid of that or change to a 0? I can live with it if I need to.

Thanks a lot!

**ChemistB** · 03-23-2010, 12:27 PM

=IF(B4="","",LOOKUP(B4,{"Day 1","Day 2","Day 3"},{40,70,110})) Does that work?

**NBVC** · 03-23-2010, 12:36 PM

Originally Posted by Squint

I did not mean to post this as solved. Do you know how I fix this or should it be deleted and reposted? I am not sure if I am able to delete my own posts. Are you able to help me with this?

Thanks and sorry for the confusion

I removed the [Solved]

**Squint** · 03-23-2010, 01:23 PM

This one works much better.

Just wondering if you can help me with 1 more I think simpler formula for
if cell a4=Lunch then B4 = 12.50?

Thanks for all your help

**NBVC** · 03-23-2010, 02:07 PM

Is that the same B4 in your previous formula?

If so, not sure that you will be able to use this formula and the one ChemistB gave you together to get final results.

If it is not the same B4 as above, then, in B4, =IF(A4="Lunch",12.5,"") formatted to 2 decimals, if desired.