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

 

[Almach]

Hey Rattling Carp,

The issue is that since C and D Return a "-" your excel is actually trying to calculate that and not a 0. So in essences your last example its going "("-"+(0/5))/12" which will throw the error.

I can think of two quick fixes. The easiest would be to have the formulas for Major and Minor return a 0 instead of a "-"

or

You can use an if statement to account for the dash, something like this. Not sure if Major and Minor can both have a "-". If so you would need write an if statement for that also.

=IF(C13="-",(D13/5)/12,IF(D13="-",(C13/12),(C13+(D13/5))/12))

 

[RattlingCarp3048]

Hey Rattling Carp,

The issue is that since C and D Return a "-" your excel is actually trying to calculate that and not a 0. So in essences your last example its going "("-"+(0/5))/12" which will throw the error.

I can think of two quick fixes. The easiest would be to have the formulas for Major and Minor return a 0 instead of a "-"

or

You can use an if statement to account for the dash, something like this. Not sure if Major and Minor can both have a "-". If so you would need write an if statement for that also.

=IF(C13="-",(D13/5)/12,IF(D13="-",(C13/12),(C13+(D13/5))/12))

how do you acct for searching the employee name?

 

[Almach]

Give me a moment I'm going to duplicate the workbook on my end to better write the formulas

 

[jtakw]

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
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

how do you acct for searching the employee name?

Hi,

I'd imagine the same way you got the C and D results ???

 

[Almach]

This is working on my end, but its all how your data set is completely laid out. I'm assuming Grossing, Embedding, and Microtomy have their own columns for major and minor values? If that's the case in the formula's below you will need to change them to the proper column reference on your Summary page for it to grab the correct Major, Minor and denominator for the weight calculation. Formula below is built for the columns in your pictures.

Major:

=INDEX(Summary!M:M,MATCH(B3,Summary!A:A,0),1)

Minor:

=INDEX(Summary!L:L,MATCH(B3,Summary!A:A,0),1)

Weight:

=IF(C13="-",(D13/5)/(INDEX(Summary!N:N,MATCH(B3,Summary!A:A,0),1)),IF(D13="-",(C13/12),(C13+(D13/5))/(INDEX(Summary!N:N,MATCH(B3,Summary!A:A,0),1))))

 

[RattlingCarp3048]

This is working on my end, but its all how your data set is completely laid out. I'm assuming Grossing, Embedding, and Microtomy have their own columns for major and minor values? If that's the case in the formula's below you will need to change them to the proper column reference on your Summary page for it to grab the correct Major, Minor and denominator for the weight calculation. Formula below is built for the columns in your pictures.

Major:

=INDEX(Summary!M:M,MATCH(B3,Summary!A:A,0),1)

Minor:

=INDEX(Summary!L:L,MATCH(B3,Summary!A:A,0),1)

Weight:

=IF(C13="-",(D13/5)/(INDEX(Summary!N:N,MATCH(B3,Summary!A:A,0),1)),IF(D13="-",(C13/12),(C13+(D13/5))/(INDEX(Summary!N:N,MATCH(B3,Summary!A:A,0),1))))

so columns C and D both have if(countifs()) statements in them with 5 different criteria established so that formula is really long. i did manage to factor in an and() statement to what you provided before which is getting me really close. what i need now is how to factor in the look up/match etc to find the employee name in B3 on the summary tab and corespond it to the count in column N....

=IF(AND(C13="-",D13="-"),"-",IF(C13="-",(D13/5)/12,IF(D13="-",(C13/12),(C13+(D13/5))/12)))

in this formula 12 represents the total count that is found in Column N on the summary tab. for this person the count is 12 but for another person it will be different.

 

[Almach]

Sorry I forgot to update one of my 12's in the weight formula. This part is finding the 12 "INDEX(Summary!N:N,MATCH(B3,Summary!A:A,0),1)"

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

 

[RattlingCarp3048]

Sorry I forgot to update one of my 12's in the weight formula. This part is finding the 12 "INDEX(Summary!N:N,MATCH(B3,Summary!A:A,0),1)"

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

minor tweak but other wise works great.

 

[jtakw]

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))