Hi,

I'm trying to average the budgets of vendors whose contracts are final
in a spreadsheet. I have 60 budgets total. Some are finalized and some
are in negotiation. Each corresponding column contains the budget
numbers, both those in negotiation and those finalized. The columns
that are final will continue to increase as budgets become final. I
can't for the life of me figure out the formula.

For example, A1, B1, and C1 contain vendor names. These cells are
shaded yellow if in negotiation or green if final. If A1 is green, I
want to include A3, A5, and A7 in the average. I want to average only
the green columns across all 60 sites. Can anyone help? I can also add
a row above the vendor names to include the word "final" if that makes
it easier.

Thanks!
Ana

On Nov 23, 3:35*pm, Ana wrote:
Hi,

I'm trying to average the budgets of vendors whose contracts are final
in a spreadsheet. I have 60 budgets total. Some are finalized and some
are in negotiation. Each corresponding column contains the budget
numbers, both those in negotiation and those finalized. The columns
that are final will continue to increase as budgets become final. I
can't for the life of me figure out the formula.

For example, A1, B1, and C1 contain vendor names. These cells are
shaded yellow if in negotiation or green if final. If A1 is green, I
want to include A3, A5, and A7 in the average. I want to average only
the green columns across all 60 sites. Can anyone help? I can also add
a row above the vendor names to include the word "final" if that makes
it easier.

Thanks!
Ana

Your version of excel ?
How did the cells become colored? Conditional Formatting or manually.
Send me your file if you like dguillett1 @gmail,com

"Ana" wrote:
For example, A1, B1, and C1 contain vendor names.
These cells are shaded yellow if in negotiation
or green if final. If A1 is green, I want to
include A3, A5, and A7 in the average. I want to
average only the green columns across all 60 sites.
Can anyone help?

Generally, you cannot test the color of cells using Excel formulas alone.
You would have to write a user-defined function using VBA. Is that
something you really want to do?

Alternatively (and better, IMHO), you would write a conditional Excel
formula using the same conditions that selected the green color.

If that is unclear and you need further assistance, it would be helpful if
you posted some details. For example, exactly what conditions are used to
select the green color -- the conditional formatting expression, or the
custom cell format?


Ana wrote:
I can also add a row above the vendor names to include
the word "final" if that makes it easier.

Yes, that should make things easier. But again, without specifics, it is
difficult to give you specific solutions that are clear.

Originally, you spoke of cells A1, A3, A5 and A7, where A1 contains the
vendor name. If you "add a row above", I presume that A1 might contain
"final", A2 contains the vendor name, and you now want to include A4, A6 and
A7 in the average if A1 is "final". Moreover, if there are 60 vendors, I
presume they are in columns A through BH.

But it is unclear what you mean by averaging "across all 60 sites". Suppose
A1 and B1 are green. Do you want AVERAGE(A4,A6,A7,B4,B6,B7), a single
value? Or do you want AVERAGE(A4,A6,A7) and AVERAGE(B4,B6,B7) each in
separate cells perhaps at the bottom of each column?

The latter is straight-forward. Enter the following formula at the bottom
of column A and copy across:

=IF(A1<"final","",AVERAGE(A4,A6,A7))

If you want AVERAGE(A4,A6,A7,B4,B6,B7) instead, if row 5 contains text in
each column, you can enter the following array formula[*]:

=AVERAGE(IF(A1:BH1="final",A4:BH7))

If row 5 does not always contain text (including if row 5 is empty) in each
column, enter the following array formula[*]:

=AVERAGE(IF(A1:BH1="final",A4:BH4),IF(A1:BH1="fina l",A6:BH7))

The second term relies on the fact that A6 and A7, for example, are adjacent
rows.
[*] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, the formula will appear surrounded by curly
braces, i.e. {=formula}. You cannot type the curly braces yourself. If you
make a mistake, select the cell, press F2, edit as needed, then press
ctrl+shift+Enter.

Sorry, I forgot to include these details. I'm using Excel 2010 and the
green color (or "final" if we decide to go that route) will be entered
manually. Don, I'll send you a mock-up shortly.

Thanks for your help!
Ana