创建多个可能结果的公式 [英] Formula to create multiple possible results

问题描述

我正在设计的电子表格旨在帮助我对我所经营的足球(或足球，取决于您来自何处)的得分.这个想法是，每个球员挑选10支足球队，他们为每个进球得分.但是，他们也选择了一支他们认为不会进球的球队.如果他们的球队没有得分，他们将获得3分，但是如果他们的球队得分，则他们将为每个进球而丢分.

例如-示例A)玩家选择曼联不进球，但得分3.这意味着玩家将得分-3分.例子B)玩家选择曼联没有进球，也没有进球.这意味着玩家将获得3分.

是否有一种方法可以为此特定选择创建公式，使其进入单元格 F13 和 J13 中，从而使A列中的团队与我的匹配已在D和H栏中输入了该团队的分数(在B栏中)是否为"0"；它会为单元格 F13 和 J13 分配3分，但是如果B列中的得分大于0(例如3)，则单元格 F13 和 J13 的读数减去B列中显示的值(例如-3).

模板电子表格可在此处找到-

您可能在 F1 单元格上使用一个公式.

  = ArrayFormula(IF(C1:C11 = TRUE，C1:C11 * E1:E11,1)) 

使用的功能:

• ArrayFormula
• IF
• VLOOKUP

The spreadsheet I am devising is to help me with the scoring of a football (or soccer depending where you're from) forum game I run. The idea is that each player picks 10 football teams and they get a point for each goal scored. However, they also pick one team which they think will not score any goals. If their team doesn't score they get 3 points but if the team does score they lose a point for every goal the team scores.

For example - Example A) The player picks Man Utd to not score a goal but they score 3. This means that the player would score -3 points. Example B) The player picks Man Utd to not score a goal and they don't. This means that the player would score 3 points.

Is there a way to create a formula for this specific selection to go into cells F13 and J13 which would match the team in the list in Column A to what I have entered in Column D and H and if the score next to this team (in Column B) is "0" it allocates 3 points to the cell F13 and J13 but if the score in Column B is above 0 (eg 3), the value in cell F13 and J13 reads as minus the value shown in Column B (eg -3).

Template spreadsheet can be found here - https://docs.google.com/spreadsheets/d/1DNOVUGPAJF-nR9XtQ1fc-lqm4fYHNBJgz7-rr6SzJaU/edit?usp=sharing (not sure if I have set it correctly to allow editing but if I have then feel free to edit away)

Hope this makes sense!

解决方案

Please use the following formula on your F13 cell (and all the corresponding ones for the rest of the players)

=IF(-VLOOKUP( D13,$A$2:$B,2,0)=0,3,-VLOOKUP( D13,$A$2:$B,2,0))

You could probably use a single formula on your F1 cell.

=ArrayFormula(IF(C1:C11=TRUE,C1:C11*E1:E11,1))

Functions used:

• ArrayFormula
• IF
• VLOOKUP

这篇关于创建多个可能结果的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！