Using formula based on text in a different cell

[Jason Pettress]

Hello. I have a list of calculations that I have to perform each week and I
decided to use excel to simplify the task. I will use generic examples. I
have a group of agents which call on any of 4 different programs. We will
say program A-D. Each program has a different goal. Each agent has an
average sales per hour for the week. Instead of pasting in a specific
formula for each different program, I want to be able to have excel
determine which formula to use based on what program.


A B C D

1 John Doe ProgA SPH %ofGoal
2 Jane Doe ProgB SPH %ofGoal
3 Dick Doe ProgC SPH %ofGoal
4 Harry Doe ProgD SPH %ofGoal

I want to be able to do the following.

If the text in column B = ProgA then divide the value in column C by GoalA
to find %ofGoal
If the text in column B = ProgB then divide the value in column C by GoalB
to find %ofGoal
etc.


I have come up with something like

=IF(B2:B56=ProgA,"=SUM(C$2:C$56/GoalA)")

but it doesn't work and it only checks for one possibility

Is what I want to do even possible? I have my spreadsheet set up so far so
that I can copy paste the information from our intranet and I have named
cells which you just enter the value for goal each week since it changes.
(GoalA, GoalB, etc.)


--
Thank You

Jason Pettress
On Site Technical Support
Global Contact Services
2200 Main Street, 3rd Floor
Wheeling WV, 26003
Phone: (304)232-7181
Fax: (304)232-7186‎

 

[Jason Pettress]

I actually figured it out myself through trial and error. I'm new to excel
so Im patting myself on the back right now, lol.

=IF(B2:B56=MGRP,SUM(N$2:N$56/GoalMGRP),IF(B2:B56=DBOA,SUM(N$2:N$56/GoalDBOA),IF(B2:B56=COOB,SUM(N$2:N$56/GoalCOOB),IF(B2:B56=MCHS,SUM(N$2:N$56/GoalMCHS),IF(B2:B56=SBBA,SUM((E$2:E$56/L$2:L$56)/GoalSBBA))))))