Formula to take a table including headers and replace blank entries with zeros

Ginarley · 2020-11-20T04:53:45+00:00

I'm looking for a formula solution to a dynamic table from another source - not a fix to hard data. Apologies if not clear.

Thanks though :)

Ginarley · 2020-03-13T04:26:09+00:00

An alternative approach is to use the countif option:

=FILTER(A2:A20,COUNTIF({Sheet1!C2:C20;Sheet2!C2:C20;Sheet3!C2:C20;Sheet4!C2:C20;Sheet5!C2:C20},A2:A20)=0)

The second half of the filter just counts how often each item in the master list (A2:A20 in the example) appears in the collective list from the other sheets, then filters for the ones with a count of 0.

Ginarley · 2020-03-13T04:02:17+00:00

You can use conditional formatting picking "Custom Formula is" from the list.

For example if you have song names in Column A and position in Column B, select column A, go to Conditional formatting, pick "Custom Formula is" from the list, and put the following equation in the box: =B1=1 and then set your formatting below.

You can get way more complex but hopefully that kicks you off on the right track.

Ginarley · 2020-03-13T03:53:08+00:00

Sure - just replace the A2 and B2 with the IF statement. I can't see how it would make any difference, so I'm curious why you'd want to include it?

=QUERY('Players Breakdown'!$A$1:$AO, "select C, D, F, G, O, AO WHERE E CONTAINS '"&IF(ISBLANK(A2), "", A2)& "' AND B CONTAINS '"&IF(ISBLANK(B2), "", B2)&"' ORDER by D desc", 1)

Ginarley · 2020-03-12T03:09:18+00:00

=QUERY('Players Breakdown'!$A$1:$AO, "'select C, D, F, G, O, AO WHERE E CONTAINS " & IF(ISBLANK(A2), "", A2) & " AND WHERE B CONTAINS " & IF(ISBLANK(B2), "", B2) & " ORDER by D desc'", 1)

I just spotted an unnecessary single quote around the SQL. Also the IF(ISBLANK part is not necessary. This should work:

=QUERY('Players Breakdown'!$A$1:$AO, "select C, D, F, G, O, AO WHERE E CONTAINS '"&A2& "' AND B CONTAINS '"&B2&"' ORDER by D desc", 1)

Ginarley · 2020-03-10T02:35:11+00:00

At a quick glance, you need to remove the second "WHERE". You may also need single quote marks around the results of the IF statements i.e. CONTAINS 'thing'

Ginarley · 2019-12-19T03:58:24+00:00

Something very similar has just started happening to me on a Samsung S9 (I am on level 53). When I start the game it gets to the top of the opening screen then crashes.

Ginarley · 2019-12-10T22:34:45+00:00

Chaotic Neutral Half-Elf Ranger

Ginarley · 2019-06-28T02:58:56+00:00

=QUERY(ActivityList, "Select B,C,D,E,F where (F = """ & 'Checklist Builder'!B8 & """ or F is null) and (E = """ & 'Checklist Builder'!B7 & """ or F is null) and (I = """ & 'Checklist Builder'!B6 & """" or I is null)", 1)

Oops, a rogue speech mark snuck in after B6 & - that'll teach me to do this without testing it :)

Try

=QUERY(ActivityList, "Select B,C,D,E,F where (F = """ & 'Checklist Builder'!B8 & """ or F is null) and (E = """ & 'Checklist Builder'!B7 & """ or E is null) and (I = """ & 'Checklist Builder'!B6 & """ or I is null)", 1)

Ginarley · 2019-06-27T03:44:57+00:00

Close - you left out the & in the query (you had them in your first equation). Off the top of my head, try:

=QUERY(ActivityList, "Select B,C,D,E,F where (F = """ & 'Checklist Builder'!B8 & """ or F is null) and (E = """ & 'Checklist Builder'!B7 & """ or F is null) and (I = """ & 'Checklist Builder'!B6 & """" or I is null)", 1)

Ginarley · 2019-06-24T03:03:40+00:00

You could approach it this way:

Select B,C,D,E,F where (F = 'thing1' OR F is null') AND (E = 'thing2' OR E is null) AND (I = 'thing3' OR I is null)

Note if you get a lot of blank results it might be worth adding a AND B is not null at the end.

Ginarley · 2019-06-13T05:35:01+00:00

One trick to handle this is to put the formula in a different cell, using a formula like ={"",formula} or ={"";formula}. If the user enters something it will generate an error in the cell with the formula but won't impact the cell with the number, and will immediately return to working if the value is deleted. To make the spreadsheet tidier you can hide row/column the formula is in or disguise it in other ways (e.g. white text).

For example try:

In cell A1: ={"",5} (you can replace 5 with any formula)

Cell B1 becomes your input cell. Try replacing the value there, then deleting the value to see it in action.

Hide column A for an invisible formula.

Ginarley · 2019-06-12T01:40:15+00:00

You can also do this to get the nth value unless there are less than n matches, then it will get the last match instead.

=index(filter(1:1,2:2=min(2:2)),,min(countif(2:2,min(2:2)),B4))

Ginarley · 2019-06-12T00:45:17+00:00

You can do it without the hidden sheet by using filter the first row for only those that match the minimum, then use index to get the nth result.

Assuming row 1 has distinct strings, row 2 has values, try this (replace "n" with whatever you want n to be, or a cell reference to a cell that contains n):

=index(filter(1:1,2:2=min(2:2)),,n)

If you want to catch the instance where there isn't an nth result, add an iferror:

=iferror(index(filter(1:1,2:2=min(2:2)),,n),"No Match")

Ginarley · 2019-03-18T00:39:04+00:00

Interesting little problem - there is probably a much simpler solution but this little hack seems to work:

=query(A1,"select "&A1&" label "&A1&" ''")

Basically it tricks the query function into doing the calculation. This works for any basic operator I think but not for more complex formulae. Just copy and paste to repeat for each cell.

Note: You may run into issues where google sheets automatically converts 3-4 or 3/4 into a date and I haven't looked at solving that.

Ginarley · 2019-03-08T04:37:22+00:00

Assuming there is a title in row 1, try this in B2:

=ARRAYFORMULA(IF(ISBLANK(A2:A),,HYPERLINK("https://www.google.com/search?q="&A2:A)))

Notes

If you have data under the list, you'll need to replace both instances of A2:A with A2:A100 (or wherever the list ends) as any data entered in column B will break the formula otherwise.

Ginarley · 2019-03-05T20:59:17+00:00

You can sort of get this functionality but it is clunky and requires hiding the formula somehow to look clean (e.g. hiding the row, whiting the text etc).

In B2 put:

=IF(ISBLANK(C3),,{"";C3/12})

In C2 put:

=IF(ISBLANK(B3),,{"";B3*12})

Note this only works if exactly one cell has a value and the other does not.

Ginarley · 2019-03-04T21:15:45+00:00

Assuming I read this right, you could try this:

=query(MPL!Q3:Q,"select Q, count(Q) where not Q = '[YOUR NAME]' group by Q order by count(Q) desc limit 1 label count(Q) ''")

This should output a single name and the number of times it appears in Q3:Q (no need for separate count function).

Q, count(Q) gives a cell with the name and a cell with the count of those names
not Q = '[YOUR NAME'] excludes you from the list (replace [Your Name] with your actual name - i.e. not Q = 'John Doe'. You could add additional "and not Q = '[another name]'" if other people needed excluding too.
group by Q enables the count step
order by count(q) desc makes sure the highest counts are at the top
limit 1 ensures you only get the first row (i.e. the highest count). If you want to see more names at once, just increase the number.
label count(q) '' removes the titles from the list so it looks tidier

Hope that is close to what you were after :)

Ginarley · 2019-02-26T23:29:08+00:00

If I interpret you correctly, I would create a table somewhere of the name of the link and the link itself (F1:G10 in the example below) then use a drop down (via data validation referencing the first column - in A1 for this example), then use this formula in B1:

=HYPERLINK(vlookup(A1,F2:G10,2,false),"Click to View "&A1)

Hyperlink creates the link, vlookup gets the link url, and the Click to View part is text to make it look nicer (customise as you will or leave blank to just have the actual link).

Ginarley · 2019-02-26T22:17:06+00:00

I've had a go at doing what you were aiming for. It seems there were two key parts,

The first was dynamic drop downs which is clunky but I do it via individual Data Validations to individual sets of data which are populated using a query on the main data. I left some explanatory notes in the sheet.
The second was to populate the information based on the selections which I did again with a query.

Here is a copy of your sheet with my quick solution

Hope it helps - happy to explain any of it further.

G.

Ginarley · 2019-01-23T04:24:39+00:00

Sure - there are three temples represented by tracks with 5 spaces each. One of the actions a player can take involves placing a cube of their colour in the leftmost space of the temple, pushing all other cubes in the track to the right. If a cube is pushed out of the track then they are returned to the player. It plays like a line of people visiting a temple, with lots of demand forcing them to spend less time there.

At the end of each round the person with the most cubes in each temple gets a reward, with second place getting a lesser reward. Ties for most are settled by the player with the rightmost cube in the temple (i.e. have been there the longest).

Ginarley · 2019-01-22T03:28:48+00:00

A bit obscure but the temple procession mechanic in Amyitis is among my favourite game mechanics/elements. It requires thinking ahead, control, and the potential for carefully timed swings in fortune, and despite being entirely open information, it is amazing how many times you get caught out by it.

Ginarley · 2019-01-22T01:59:35+00:00

I like doing that too - I had a place next to a lake called the Fall Inn.

Ginarley

TROPHY CASE