Help needed with complex expression

Johnny_Cardel · 02-21-2021 09:32 AM

Hi guys, I need to show column (QC* Question Column) depending on the value in other columns (AC* Answer Column), this is to show the next question only when all 4 players have answered, Q1 show Q2 pretty straight forward… AND([answer 1 Player 1] >0, [answer 1 Player 2]> 0, [answer 1 Player 3]> 0, [answer 1 Player 4]> 0)
But if there is only 3 or 2 players then the value of 3 or 4 player will always be 0 so it will not show, i have built a column with the number of player, so need a IF column Number of Player is equal to 2 player then check only 1 & 2, IF column Number of Player is equal to 3 player then check only 1, 2 & 3 etc…
can anyone please guide me with this one
TIA

Steve

Maybe this?

(
  COUNT(
    TOP(
      LIST(
        [Answer 1 Player 1],
        [Answer 1 Player 2],
        ...,
        [Answer 1 Player N],
      ),
      [Player Count]
    )
    - LIST("")
  )
  = [Player Count]
)

LIST(...) creates a list of all the Answer 1 values, one answer per player, in player-number order.
TOP(..., [Player Count]) extracts from the list in (1) only the answers for the number of players.
... - LIST("") removes from the list in (2) any blank answers.
(COUNT(...) = [Player Count]) counts the number of non-blank answers (from (3)) and compares that number to the number of players. If the numbers are equal, all players have provided a response.

Replace Player Count with the name of the column containing the player count.

See also;

Johnny_Cardel

??? thanks for you reply but I don´t understand any of that, can you explain please,

Steve

Post above updated with greater detail.

Johnny_Cardel

What a fast reply, thanks, am looking into it have some errors, could not fine [Answer 1 Player N], whats the N, I changed it to I added player 3 and 4 under [Answer 1 Player 2],and changed the player count column it shows if player 1 score is above 0 regardles of the amount of players, this is what I ended out with
(
COUNT(
TOP(
LIST(
[Score Hole 1 Player 1],
[Score Hole 1 Player 2],
[Score Hole 1 Player 3],
[Score Hole 1 Player 4],
),
[Number of Players]
)
- LIST("")
)
= [Number of Players]
)
Thanks again