|
I'm not sure exactly where you're calculating these but if you're calculating the average for a given week in the player's sheet then pulling that into the main summary you can use the iferror with the average so the error never shows. So something like =IFERROR(AVERAGE(<range of values for the week>),""). Then if you create a total average on the main summary, Excel will ignore the blanks. This will also lend itself to finding the highest average because then you can use large and reference your range of weekly averages, something like =LARGE(<range of averages>,1). The second parameter tells it to return the largest value in the range if = to 1, second largest if = to 2 and so forth.
|
#
¿
Oct 31, 2020 16:06
|
|
|
|
| # ¿ May 11, 2024 23:51 |
|
|
If the value you're looking for will only appear once in a cell or you only want to count the cell once then you can use =countif(range,"*value*"). If the value is going to appear more than once then you would want to use something like =(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word). Note that substitute is case sensitive. This formula only works on a single cell at a time so you would need to sum over the results.
|
|
#
¿
Jan 15, 2021 22:34
|
|
|
Effectively what it's doing is counting the number of characters total then subtracting the number of characters not associated with your value which gives you the number of characters associated with your value. Then it divides that by the number of characters in your value to give you the number of times your value appears. So if you took a string like "bye hi hi bye" and used =(LEN("bye hi hi bye")-LEN(SUBSTITUTE("bye hi hi bye","hi","")))/LEN("hi") it would calculate to (13-9)/2 meaning it was 13 characters long, 9 of which were not associated with your word. So your word took up 4 characters. Then it divides by the length of your word which was 2 meaning your word "hi" appeared twice. Substitute in this case is reducing the total length of your input string by however many characters your target value is multiplied by the number of times it is present by substituting any instances it finds of "hi" with "" (nothing). I'm not sure if I made that any clearer but let me know if that makes sense. You could also make this not case sensitive by using UPPER or LOWER on your string and your search term. So the substitute portion would become: SUBSTITUTE(UPPER("bye hi hi bye"),UPPER("hi"),"") The rest would stay the same as LEN() won't care about case.
|
|
#
¿
Jan 15, 2021 23:21
|
|
|
Kibayasu posted:The asterisks in “*value*” just means replace the *value* with what you wanted to look for. Quotation can be quite important in excel functions so it’s important to make sure you don’t exclude them in a “Enter [blank] without the quotes” way. Chances are you actually do need the quotes. I should have been more clear on that, I was using the asterisks there as a wild card since countif allows those and I wasn't sure where in the cell his value would be. So in my previous example if you wanted to use countif to count a cell containing "bye hi hi bye" you would use =countif(<range>, "*hi*") where the range is the address of the cell (such as A1 or RC[-1] and you can have multiple cells since it's a range such as A:A for column A or C[-1] for the column left of the cell) but as I said before it would only count each cell once regardless of how many times the criteria appeared in that one cell. The * in that example allows any number of characters (including none) to be before or after 'hi'. If you ever wanted it to be exactly 'hi' you would just use =countif(<range>, "hi"). If you wanted a specific number of characters before your search term in the criteria, like say you wanted 4 characters before and any number of characters after your search term, you would use =countif(<range>, "????hi*") where ? represents a single wildcard character (can be useful for validating the formatting of strings). Countif is particularly useful because it allows wildcards and a lot of excel formulas don't.
|
|
#
¿
Jan 17, 2021 06:21
|
|
|
Hughmoris posted:I did a poor job fully explaining my problem. The issue being the event could span multiple days, so I need to take the dates into account as well. Doing =(Day 2 - Day 1)*24 + HOUR(Time 2 - Time 1) + (MINUTE(Time 2 - Time 1)/60) should work if you want it as a decimal. If you want the formatted number of minutes you could massage it more but I like decimals. for example =(C3 - A3)*24 + HOUR(D3 - B3) + (MINUTE(D3 - B3)/60)
|
|
#
¿
Nov 27, 2023 22:48
|
|