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.

Improve this question
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!
    – Cristian Gabriel
    Nov 21, 2021 at 10:05

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.