formula troubles

[RattlingCarp3048]

another formula kicking me in the back side this week tgif cuz my brain is not working today.

In E13 i have a base formula to insert which is =((major+(minor/5))/total count). i will repeat the formula for each of the 3 categories: grossing, embedding, microtomy. starting with grossing, Major =C13, minor =D13, total count is on another sheet Summary Column N.

in this case, E13=look up the employee name in Sheet1 B3 which is somewhere in the summary tab column B. then calculate ((1+(1/5))/12) =0.1

sheet 1

summary tab

problems:
- C and D are both "-" returned from a formula.
- there may not be a value in C so the formula would only need to calculate D
- there may not be a value in D so only calculate C
- not sure how to incorporate looking up the employee name

 

[RattlingCarp3048]

Just to give you a couple of options, the formula can be shorten to one of these:

=IF(AND(C13="-",D13="-"),"-",(N(C13)+N(D13)/5)/INDEX(SUMMARY!N:N,MATCH(B3,SUMMARY!B:B,0),1))

Or

=IF(AND(C13="-",D13="-"),"-",(N(C13)+N(D13)/5)/VLOOKUP(B3,SUMMARY!B:N,13,0))

Thanks Jtakw! Ill look into those options as well. At least for now i have options