Correct syntax for function to find average based on background color of cells in range

Good afternoon. I'm working on a workbook that will track staffing patterns.

The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".

On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

Please Login or Register  to view this content.

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

Please Login or Register  to view this content.

I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.

Please Login or Register  to view this content.

How might I use the following functions to find the average of the source fields instead of the SUM?

I appreciate any feedback you might be able to offer to me. Thanks.

Matthew

Can you:

1) upload a fully unlocked workbook
2) specify exactly what cell has a formula you're using and what the desired result is in that specific cell over the value your formula is offering

Hi Matthew

What do you wish to do if "Productivity" is blank...count it or skip it?

If you wish to exclude it try this

Formula:

Please Login or Register  to view this content.

JERRY,

I'll upload an unprotected version once I'm back from my daughters track meet. I'll explain a bit more then as well.

John,

Great question. I hadn't given it any thought since there is likely never going to be a time where the productivity will be blank. I guess if I had to choose I would opt for including it in the equation. Thanks.

In the end I will be showing the totals in a userform that will be a summary of both services. Thanks.

Matthew

Matthew

Hi Matthew

To include use this

Formula:

Please Login or Register  to view this content.

Sheet2 "HCT" Column J contains subtotals of individual worker productivity (%). At any given time we may be tracking 3 to 7 employees so the number of subtotals that I will be averaging will vary. I need to average the subtotals to come up with a grand total for the productivity of all employees working within HCT services.

Please Login or Register  to view this content.

Sheet1 "RCS" Column I contains subtotals of individual worker productivity (%). At any given time we may be tracking 3 to 7 employees so the number of subtotals that I will be averaging will vary. I need to average the subtotals to come up with a grand total for the productivity of all employees working within RCS services.

Please Login or Register  to view this content.

The two lines of code above give me the SUM of all the percentages and not the average. Some how I need to figure out how to get the average % of all the subtotals. Hope this helps.

Thanks.

Matthew