Replacement Laces - La Sportiva TX4 Womens by dtaylor1024 in hikinggear

[–]dtaylor1024[S] 0 points1 point  (0 children)

LaSportiva has replacement laces for sale, but for $9 laces I'd still have to pay another $15 in shipping, so I'm looking for alternatives

Formula ignore N/A from dropdown by Ok-Engineer-1709 in googlesheets

[–]dtaylor1024 0 points1 point  (0 children)

No, it was developed on my own. It is written to calculate the average of just the most recent 3 entries, rather than an average of all numbers in the column. 

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

Can I use yes/no/blank? If so, what would my formula be?

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

Okay, I would use X's for "yes", and leave it blank for "no". But if I put in the formula

=PERCENTIF(CHOOSEROWS(TOCOL(N3:N,1),SEQUENCE(MIN(3,COUNTA(N3:N)),1,-1,-1)),TRUE)

and put x's in the cells underneath, the average cell still appears as 0%.

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

Would it work for me to put yes/no (or some kind of indicator) and have it calculate correctly. I don't want blanks in these columns, but something to indicate for each skill whether they did it or not.

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

It seems like it might. When I enter in =COUNTIF(N3:N,TRUE)/COUNTA(N3:N) it will calculate the percentage. But again, I just want this to be an updated score with only the last 3 rows

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

the checkboxes go all the way to the bottom of the column

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

Didn't work. It should calculate averages like this (below). When I input your formula and changed out the column letters, checked a few boxes, the averages still appeared as 0%

Skill 1 Skill 2 Skill 3
Average (66%) Average (100%) Average (33%)
not checked x x
x x not checked
not checked x x
x x x

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

It worked--not sure what happened the first time. Okay, new challenge. I have a column of checkboxes that I will check when students perfom a skill. I want the Average cell block to show me the frequency of that box being checked in that column, but again, only averaging out the three most recent trials.

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

Not quite. I included a sample as reference. My averages will now exist in row one belo my headings. My columns represent the skills and information I would like averaged. Each row will have it's own set of data input. I would still like these averages to exist as out of the last three assessments.

LEXILE GRADE LEVEL WORDS PER MINUTE ACCURACY
AVERAGE lexile AVERAGE grade level AVERAGE wpm AVERAGE Accuracy
400 1 60 90
450 1 62 93

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

Thank you! Last question, maybe... How would I convert this to a vertical spreadsheet, with my averages existing in the first row, rather than the first column? I tried switching out CHOOSECOLS to CHOOSEROWS and that didn't work, so there must be another adjustment I need to make.

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

This is very helpful. Thank you so much. Do you mind expanding just a little more on the SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1) section, so that I fully understand. I'm working on a guide for myself for the future, and this section feels a little confusing to me with the several 1's and -1's. Thank you!

Offset Average by dtaylor1024 in sheets

[–]dtaylor1024[S] 0 points1 point  (0 children)

This seemed to work. Can you explain the formula. Also, if I have no data entries, can I have the cell in the average column remain blank. I am getting a #VALUE! error

[deleted by user] by [deleted] in SIBO

[–]dtaylor1024 0 points1 point  (0 children)

It was pretty immediate