asked on

Excel calculations

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

Rob Henson

We can't look at your example because it is password protected.

Thanks
Rob H

Rob Henson

Sorry, was able to open without password; I had clicked Cancel before.

SOUTHAMERICA70

ASKER

oops.....sorry about that, the password is 787

barry houdini

It's difficult to see what you want to do? You are talking about A, B C and D but there are multiple rows of those, are you only looking for results in P93 and Q93?

regards, barry

SOUTHAMERICA70

ASKER

Barry....

Basically.....in either one of the A, B, C, D gets a number in it regardless in which column it counts, for example....if they put a numerical value in any A that is considered to be one evaluator and if a numerical value is in B that is considered to 2 evaluators so on P93 and Q93 that would be P92 and Q92 divided by 10.08

Now if there is a numerical value in any C as well that would count as 3 evaluators and now that would be divided by 15.12.

On the attachment there is a number in A, B, C, D so that would be divided by 20.16....

barry houdini

So, for A are you looking at just cells B73:B88 or also G73:G88 and L73:L88? I understand that you want to divide by 10.08 or more if there are 2 or more "evaluators"....what if there is one or zero, do you not divide by anything.....or return some other result?

regards, barry

SOUTHAMERICA70

ASKER

Yes.....looking at all A's thru D on every column. They will never enter a zero and they can leave it blank. The Average and SUM columns auto calculate. So basically I just need the Composite Risk Score divided by how many evaluators, either being 2 = 10.08, 3=15.12 or 4 - 20.16 based on if they enter a number in either A, B, C, D.

I mean they can enter 1 in B73 and 7 on O77 that would equal to 8 so that would be divided by 10.08, the result would be in Q93.

or
They can enter 4 in G73, I87, I88 and 7 in M84 that would equal 19 divided 15.12 (3 Evaluators) and the result would be in Q93.

barry houdini

OK you could use this formula for P93

=P92/((COUNT(B73:B88,G73:G88,L73:L78)>0)+(COUNT(C73:C88,H73:H88,M73:M78)>0)+(COUNT(D73:D88,I73:I88,N73:N78)>0)+(COUNT(E73:E88,J73:J88,O73:O78)>0))/5.04

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

SOUTHAMERICA70

ASKER

Very close.....thank you!

There will always be a minimum of 2 evaluators so at least Columns A, B, C, D will have numbers and that gets divided by 10.08.

It would never be divided by 5.04.

barry houdini

OK, if there will always be numbers in at least 2 of the evaluator columns then the above should work, I think. This version will return "Error" if there are zero or one

=IFERROR(P92/CHOOSE((COUNT(B73:B88,G73:G88,L73:L78)>0)+(COUNT(C73:C88,H73:H88,M73:M78)>0)+(COUNT(D73:D88,I73:I88,N73:N78)>0)+(COUNT(E73:E88,J73:J88,O73:O78)>0)+1,"x","x",10.08,15.12,20.16),"Error")

regards, barry

SOUTHAMERICA70

ASKER

I get a #NAME? error

SOUTHAMERICA70

ASKER

Could you use this........it shouldn't prompt you for a password, but if it does it's 787. MSE-CODED.xls

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial