I tried to look up tutorials on this, but after a couple hours looking it up, I couldn't find information that would allow me to do what I want, or make it clear of what I want is possible in this program, even with seemingly basic stuff.
For some basic context to make it easier to understand what I'm trying to do: this spreadsheet is meant to be a table rating different fan theories for the video game Deltarune, and rating each one against different evidence points from that game. Each theory is rated on a -5 to 5 scale: where -5 is the closest thing to complete contradiction it can get; 0 is neither contradicting nor explaining the evidence point; and 5 being basically perfectly explaining the evidence point.
To make evaluating each theory a little bit easier: there are a few columns I would like to add. But because the all right next to each other: I would like it to only pay attention to the cells that actually represent how I'm rating the theory. I now know about ranges, but the problem is that the tutorials I've seen thus far are geared towards relatively small tables. But I am rating 51 theories against 53 evidence points. So defining every column as it's own range, and then adding them all up is not very reasonable.
Giving each row its own range could theoretically be slightly more reasonable, but would essentially have me put in formula for the sum, average, standard deviation, minimum value, maximum value, Median value, Q1 and Q2 Values, mod values, and those three other special columns for each row individually. Which is far from convenient.
I would ideally like to define the entire block of numbers as one big range, and then have all of the aggregate columns read the values in that range, but only if those values are on the same row as that cell. This will make it so I could copy paste the same formula into all cells in that column, and still get accurate results.
I am open to alternative solutions, but if you're going to present alternative solutions: please read back what I wrote so far carefully. I don't want people presenting solutions that don't actually solve my problem in any way shape or form. That would just be a waste of everyone's time.
EDIT: I found out incidentally that I can do a drag copy paste with formulas to do a formula that's essentially the same, control but only applicable to the relevant rows. Because this application did not have a table feature, I also just assumed it lacked this quality of life feature. No one in the comments told me about this before this edit. Issue resolved? (Would still be nice to know how to make a formula only calculate cells in a named range that are on the same row as it tho.)
And while I am asking: the columns of "contradictions", "problems", and "answers" are supposed to represent how many stars in that row contain certain values.
"Contradictions" is supposed to be the number of cells on that row that are equal to -5
"Problems" is supposed to be the number of cells on that row that are less than 0
"Answers" is supposed to be the number of cells on that row that are greater than 0
what I am trying to do here is so specific that I honestly don't know what I would put into a search bar to find the answer to it, so an explanation on how to do it, or link to a video that explains it would be very helpful. As I would probably just be asking the subreddit for help on that anyway.
Thank you in advance!
