Weighted Average Credit Score

[AltaJJ]

Hello All,

New member that is trying to get a weighted average result from a data set that includes 0s and blanks.

Here is the formula I'm currently using:
=IFERROR(SUM(IF('Trial Balance -2020-03'!$AI$2:$AI$10000='Score Cards'!A4,'Trial Balance -2020-03'!$H$2:$H$10000*'Trial Balance -2020-03'!$U$2:$U$10000)/SUMIF('Trial Balance -2020-03'!$AI$2:$AI$10000,'Score Cards'!A4,'Trial Balance -2020-03'!$H$2:$H$10000)),0)

The "AI" column is the description name, "H" column is balance, "U" column is credit score. The issue is that some of the data in the "U" column is blank or zero, and the weighting is being applied to that. Is there an additional "if" formula that I can add so that it only takes the weighting of the non-blank or non-zero cells by the description name.

Lastly, someone at my prior company helped me with this original formula and told me it is an array formula that needs the {}. It seems to work with those, however. Is this truly an array formula?

Thanks!!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

You don’t need to enter formulae as array formulae in MS365 - CSE won’t be needed.

[AltaJJ]

Thank you, AliGW. I have attached the sample file. I hope it makes sense. As you can see, the weighted average credit score for customers of dealer 1 is 580; however, it is being skewed by the missing FICOs highlighted in the "Details" tab.

Thanks!

[FlameRetired]

Your data in Details!$AI$2:$AI$35 has some trailing spaces. In fact all Dealer 3s have them.

[AltaJJ]

Thanks, Dave. You're right; that must have happened when I changed the name and copied it. I've attached a revised file.

[AltaJJ]

Anyone? Hoping to get my report out today, if possible. Thanks in advance for any help.

[FlameRetired]

Thank you, AliGW. I have attached the sample file. I hope it makes sense. As you can see, the weighted average credit score for customers of dealer 1 is 580; however, it is being skewed by the missing FICOs highlighted in the "Details" tab.

Thanks!

I am afraid I don't understand the logic of your request and the formula you are using. I am also unfamiliar with how credit scores are determined ... (the specific math/logic)

Please tell us the specific value you would expect if you did your calculations manually. Then please explain the logic step by step that you used to get that value.