HERE IS WHAT I'M TRYING TO DO. WANT TO CALCULATE A NUMBER DIVIDED BY WHAT IS FILLED OUT.

FOR EXAMPLE....COLUMN B,C,D,E ( RATER) , G, H, I, J, AND L,M,N,O IS WHAT I WANTED TO CALCULATE.

SO IF THOSE COLUMNS (RED) ARE FILLED IN FOR A,B,C,D EACH LETTER REPRESENTING AN EVALUATOR THEN DIVIDE THE NUMBER BASED THE REQUIREMENTS ON G63.

SO IF ANY COMBINATION IS FILLED OUT FOR EXAMPLE RATER A AND RATER D HAS A NUMBER IN IT THAT WOULD BE CONSIDERED 2 SO DIVIDE THE Q92 BY 10.08.

NOW IF ANY OF THE RATER COMBINATION OF A,B,C THEN DIVIDE Q92 BY 15.12

IN THE ATTACHMENT, RATER A,B,C,D HAS A NUMBER IN IT SO Q92 WILL BE DIVIDED BY 20.16 WHICH EQUALS TO 7.73 IN Q93 AND 1.93 IN P93.

THE GREEN BOX IS WHAT I WANTED TO DIVIDE BY AUTOMATICALLY ONCE THE RATER FILLS IN A NUMERICAL VALUE IN EITHER OF THE A, B, C, D COLUMNS MENTIONED ABOVE.

MSE--Coded.xlsx

FOR EXAMPLE....COLUMN B,C,D,E ( RATER) , G, H, I, J, AND L,M,N,O IS WHAT I WANTED TO CALCULATE.

SO IF THOSE COLUMNS (RED) ARE FILLED IN FOR A,B,C,D EACH LETTER REPRESENTING AN EVALUATOR THEN DIVIDE THE NUMBER BASED THE REQUIREMENTS ON G63.

SO IF ANY COMBINATION IS FILLED OUT FOR EXAMPLE RATER A AND RATER D HAS A NUMBER IN IT THAT WOULD BE CONSIDERED 2 SO DIVIDE THE Q92 BY 10.08.

NOW IF ANY OF THE RATER COMBINATION OF A,B,C THEN DIVIDE Q92 BY 15.12

IN THE ATTACHMENT, RATER A,B,C,D HAS A NUMBER IN IT SO Q92 WILL BE DIVIDED BY 20.16 WHICH EQUALS TO 7.73 IN Q93 AND 1.93 IN P93.

THE GREEN BOX IS WHAT I WANTED TO DIVIDE BY AUTOMATICALLY ONCE THE RATER FILLS IN A NUMERICAL VALUE IN EITHER OF THE A, B, C, D COLUMNS MENTIONED ABOVE.

MSE--Coded.xlsx

regards, barry

regards, barry

=P92/((COUNT(B73:B88,G73:G

and the same for Q93 except replace P92 at the start with Q92

.....that should work if there are values in 2, 3 or 4 of the rating categories...but my previous question was about the situation where you have numbers only under 1 category....or none, I don't believe the formula will work in those cases. What should the divisor be if that is the case......or do you divide at all?

The above formula will give an error if there are no numbers in the A, B, C or D categories......and if there are only numbers in one category it will divide by 5.04, what should happen?

regards, barry

=IFERROR(P92/CHOOSE((COUNT

regards, barry

This formula will work in any version, though

=CHOOSE((COUNT(B73:B88,G73

Note: I had to correct some of the ranges which only went down to row 78 (rather than 88) for some columns in my previous replies......

and almost the same or Q93 except P92s changed to Q92s

see attached

If you remove the figure from M84 you get "Error" because you only have values for one "evaluator"

regards, barry

MSE-CODED-barry.xls

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial