Calculations based on adjacent cell values

J

Jack

Hi!

I have a spreadsheet with a column that is either Red or Blue, and I'd like
to do AVG, MIN, MAX, and MEAN for the column adjacent to it. Is there a way
to do these calculations based on the adjacent cell of Red or Blue? I'd sort
the data and do it that way, but I need to do this with a number of different
columns, so I need to figure out a way to make it conditional.

Thanks and hope that makes sense!
 
M

Marcelo

Hi Jack,

try the tips on the Chip Person web site

hope this helps
regards from Brazil
Marcelo

"Jack" escreveu:
 
M

mrice

You could use a user defined function to display the colorindex value of
the cell interior.

Function CellColour(Cell As Range)
CellColour = Cell.Interior.ColorIndex
End Function
 
J

Jack

Sorry - I guess my original post wasn't clear. The cells aren't colored,
"Red" and "Blue" are the actual entries in the cells. I just need to do
calculations on adjacent cells based on whether they are in the "Red" or
"Blue" group.
 
A

Ardus Petus

Say you have col A = "Red" or "Blue"
colB = your values
cell C2 = "Red" or "Blue"
In D2 thru G2, enter formulae:
=AVERAGE(IF(A:A=$C2,B:B))
=MIN(IF(A:A=$C2,B:B))
=MAX(IF(A:A=$C2,B:B))
=MEDIAN(IF(A:A=$C2,B:B))

HTH
 
Top