0

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.

3
  • 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.
    – Tedinoz
    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?
    – Tedinoz
    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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.