Apple Numbers: Help for creating formula (pseudocode

Question

Level 1

4 points

Apple Numbers: Help for creating formula (pseudocode

Hi all,

can someone please help me to implement a formula in Apple Numbers -- for cell B2 in Table 2 -- doing the following (pseudocode):

// 1. Step
my_performance = 0;

for_each( row : Table1 )
{
  if( Table1.B.row == "John" )
  {
    num_guests = (if (Table1.D.row != none) 1 : 0) + // add "+1" if Guest 1 is not empty
                 (if (Table1.E.row != none) 1 : 0) + // add "+1" if Guest 2 is not empty
                 (if (Table1.F.row != none) 1 : 0) ; // add "+1" if Guest 3 is not empty
                 
    my_performance += Table1.C.row * num_guests;
  }
}

// 2. Step
others_performance = 0;

for_each( row : Table1 )
{
  if( (Table2.A.2 == Table1.D.row) OR
      (Table2.A.2 == Table1.E.row) OR 
      (Table2.A.2 == Table1.F.row)  )
  {               
    others_performance += Table1.C.row;
  }
}


// Result
Table2.B.2 = total_performance - others_performance;

Many thanks in advance for any help!

Posted on Feb 4, 2025 2:39 AM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Feb 4, 2025 1:09 PM in response to AriGER

It would help masssively to see what data we're working with here.

The first part of your request - my_performance - is easy, although I have no idea what value is supposed to be in Table1:C, which complicates things a little), so maybe my interpretation is wrong. Here's how I did it, though:

Ostensibly, I added an extra Column at the end. the formula in G2 is:

=C2×COUNTA(D2:F2)

Where C2 is the mysterious (to me) column C, and COUNTA() counts the number of non-blank cells in the range D2-F2 (the guest names?). This gives the 'performance' value for that row.

Then, separately, in Table 2, I use a SUMIFS) to count the 'Performance' Values for John:

=SUMIF(Table 1::B,"John",Table 1::G)

This looks at the Name column in Table 1 and matches cells that contain "John". For each match it adds the corresponding Performance value, giving you your performance score.

I'm stuck, though on the other two asks.

For "Others" performance... I don't have a clue what's in Table2.A.2 to be able to know what I'm comparing. IS this some other player's name? in which case you can just repeat the SUMIF() function above, substituting A2 for "John".

For the 'result', you're referencing total_performance which is not defined anywhere, so I have no idea what this represents.

Reply