# How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)

I've been trying to create a scoring system for expected stock market returns - Sheet

I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS

I want the formula to return a score of 0 if the return is below -20% in each of the 6 scoring system in the right, I want it to return 1 if the value is between -20 and -15% and so on until it returns 10 if the expected return is over 30%.

It's probably a use of the `IFS` formula, but I just can't get it to work with %.

-Later Edit: Managed to do fix them

1. `=arrayformula(IFS(H8:H="","",H8:H<=-15%,0,H8:H<=-10%,1,H8:H<=-5%,2,H8:H<=0%,3,H8:H<=5%,4,H8:H<=10%,5,H8:H<=15%,6,H8:H<=20%,7,H8:H<=25%,8,H8:H<=30%,9,H8:H>30%,10))`

2. I want to create an arrayformula with a weighted average formula based on the weight of each category: dcf low should have a 7.5% weight, dcf mid should have a 15% weight, dividends should have a 5% weight and so on. I'm trying the average.weighted function, but I can't really nail it.

This worked for me, but I would have liked to put it in an arrayformula but it simply gave errors when I tried.

`=AVERAGE.WEIGHTED(P9,\$P\$6,Q9,\$Q\$6,R9,\$R\$6,S9,\$S\$6,W9,\$W\$6,X9,\$X\$6,Y9,\$Y\$6,AA9,\$AA\$6,AB9,\$AB\$6,AC9,\$AC\$6)`

The issue is now when I try to use toe sort function to sort by the overall score, it gives me an error saying that that will overwrite data in W8 but I simply don't understand why.

• Hi and welcome. FWIW, this formula (as written) : `=arrayformula(IFS(H8:H="","",H8:H<=-15%,0,H8:H<=-10%,1,H8:H<=-5%,2,H8:H<=0%,3,H8:H<=5%,4,H8:H<=10%,5,H8:H<=15%,6,H8:H<=20%,7,H8:H<=25%,8,H8:H<=30%,9,H8:H>30%,10))` is a disaster. Your sheet initially contains 100 rows. However when that formula executes, it just keeps adding rows. The number of rows on the sheet (before I stopped it) had automatically grown to over 30,000 rows. Yikes. Nov 21, 2021 at 2:59
• when I try to use toe sort function to sort by the overall score, it gives me an error Based on your spreadsheet, I tried two methods to sort by overall score and I couldn't reproduce your error. Would you please describe the EXACT steps that you follow to sort by overall score? Nov 21, 2021 at 9:02
• Hello, so... I haven't had the problem with the formula adding rows so I don't know why that is happening in your trials. I am creating the overall score based on the weights and scores given by that formula. I managed to fix the error by puting the scores in another sheet and where there are no formulas , and using the SORT function and then importing back again in a display sheet. Probably not the best fix, so I decided to leave the sheet as it is and manually sort it out whenever I need to check, so I can sort it by other columns if I need as well. Thanks very much for the input anyways! Nov 21, 2021 at 10:05