We help IT Professionals succeed at work.

Excel calculations

SOUTHAMERICA70
SOUTHAMERICA70 asked
on
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
Comment
Watch Question

Rob HensonFinance Analyst

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

Thanks
Rob H
Rob HensonFinance Analyst

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

Author

Commented:
oops.....sorry about that, the password is 787
Most Valuable Expert 2013

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

Author

Commented:
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....

Most Valuable Expert 2013

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

Author

Commented:
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.
Most Valuable Expert 2013

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

Author

Commented:
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.



Most Valuable Expert 2013

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

Author

Commented:
I get a #NAME? error

Author

Commented:
Could you use this........it shouldn't prompt you for a password, but if it does it's 787. MSE-CODED.xls
Most Valuable Expert 2013
Commented:
I assume you get #NAME? from IFERROR function which is only available in Excel 2007. Which version are you using - your first file was in excel 2007 format.....?

This formula will work in any version, though

=CHOOSE((COUNT(B73:B88,G73:G88,L73:L88)>0)+(COUNT(C73:C88,H73:H88,M73:M88)>0)+(COUNT(D73:D88,I73:I88,N73:N88)>0)+(COUNT(E73:E88,J73:J88,O73:O88)>0)+1,"Error","Error",P92/10.08,P92/15.12,P92/20.16)

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

Author

Commented:
Thanks Barry for your patience and great work!!!

Author

Commented:
AWESOME!