Numbers Formula Assistance

First off:

> I use D54, 100000, just to simplify the creation of formulas for the D column, but if I could get rid of it, I would.

What would you replace it with? I assume that's an earnings target... that needs to be defined somewhere, so I'm not sure what you're thinking here.

Either way, for the Weekly Req. formula, a slightly simpler/cleaner approach would be to change your column A to be just numbers. Since you already have 'Week #' in the header, it seems redundant to then also have 'Week' in the cell value. Replacing this column with simple numerics would simplify the calculation since you can then just divide by the value in column A:

=(Weekly Req. $Total−SUM($C$2:C2))÷(52−$A2)

In this way it automatically subtracts the week number from 52 before performing the division.

If you want to keep the 'Week' label in column A, you can do that - you just need to be more creative in extracting the number from the 'Week' label, such as:

=(Weekly Req. $Total−SUM($C$2:C2))÷(52−VALUE(TEXTAFTER($A2," ",1)))

which takes everything after the space in cell $A2.

As for the second ask:

> I would like to get a separate percentage of how many red and how many green cells I have in column C while not counting blank cells.

That's easiest done via an extra/hidden column.

I added new column D that simply calculates the Weekly Gross Earnings minus the Weekly Required and sets a 1 or 0 value depending on whether it's over or under the goal:

D2:

=IF(C2<E2,1,0)

Now I have a column of 1s and 0s which are easy to factor.

I broke out a separate table to make it easy to show. hopefully you can use this to work out the logic:

Note the use of COUNTIFS() in the Over/Under calculation - this makes sure there's a value in the Weekly Gross Earnings field and therefore excludes empty weeks. Once you have the counts for weeks, overs and under, the percentages are easy to calculate.

Instead of the number 51 in row 3's formula you could use (54-ROW())

Fill the formula down to the other rows

Alternatively you could use (52-ROWS($C2:C2))

The first one is simplest but has a dependence on where the formula is in the table; what row it is in. If you add another header row it will throw off the results and require editing the formula. That is not really a big deal, just something to be aware of. The second one is based on how many rows were used in the SUM. It is not as simple but is not dependent on what row the formula is in, just on how many rows are in the SUM.