﻿ Overall rating in financial risk matrix - Excel User Group Excel User Group Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Overall rating in financial risk matrix

 Previous | Next
Latest post Sun, Mar 8 2015 11:56 PM by bobbell. 3 replies.
• Fri, Aug 12 2011 7:45 AM

• Pragna
• • • Joined on Thu, Jun 16 2011
• Posts 6
• Points 110

Overall rating in financial risk matrix

 Hello all,  question - I am calculating an overall risk rating for accounts.  I am averaging values but if the value is zero in one cell, I would like to not include it in the averaging.  Can someone please help me how to set this formula up?  here is what I have for now - =AVERAGE(SUM((Q28*Q\$22)+(R28*R\$22)+(S28*S\$22)+(T28*T\$22)),P28) cols q,r,s,t have values of 1-2-3 which compute with percents in row 22 (percents represent the risk weighting of each factor.  However I am not applying a 1-2-3 to materiality.  Therefore when computing the overall rating of the financial account  I would like to take the risk factors of each category and include materiality.  Materiality for now has been defined as follows: anything less than \$500 = NA \$500 = \$10,000 = small given a rate of .5 \$10,000 to \$100,000 = medium given a rate of .75 Greater than \$100M = Large given a rate of 1.0 the rates to materiality implies that if the account has a high balance, it should be accounted for in the overall rating at 100%, medium and small accounts to be discounted as noted. If there is another way to present the formula please let me know. Appreciate your help.  Thank you and Happy Friday!
• Post Points: 21
• Fri, Aug 12 2011 12:10 PM In reply to

• Jim Cone
• • • Joined on Tue, Jan 15 2008
• Portland, Oregon - USA
• Posts 243
• Points 3,314

Re: Overall rating in financial risk matrix

 Comments...You shoudn't use plus or minus signs and the Sum function together.Either...=Sum(6, 7, 8) -or-= 6 + 7 + 8 The Average function ignores blank cells and cells with text but inclues zero values. Should you not apply "materiality" to each individual account before averaging them? What is P28? What exactly is your question?'---Jim ConePortland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (Extras for Excel add-in:  convenience built-in)
• Post Points: 37
• Fri, Aug 12 2011 1:54 PM In reply to

• Pragna
• • • Joined on Thu, Jun 16 2011
• Posts 6
• Points 110

Re: Overall rating in financial risk matrix

 I was trying to arrive at an overall rating of account balance significance and the various risk factors.  Instead of averaging I multiplied P28 which has the factor for account balance significance (large account =1, med=.75 and small=.5). I am good for now.  Thank you for your time.
• Post Points: 5
• Sun, Mar 8 2015 11:56 PM In reply to

• bobbell
• • • Joined on Mon, Mar 9 2015
• Posts 1
• Points 5