Support NeoGAF

[lachesis]

Being just a mere graphic designer, I'm not too farmiliar with Exel... but here it goes anyhow, hoping someone will be able to help me out!

1. I have bunch of numbers varing from 60-99 in hundreds of different cells in many rows and columns. I would like put heirachy in those numbers, so that 70s in yellow, 80s in orange, and 90s in red. Is there any way to do so without changing the color of the number or cell color manually?

2. I also would like to know how to average the numbers in one column.

I'm using Office 2000 on 2000 based PC - any help would be greatly appreciated!

lachesis

 

[Justin Bailey]

not sure about the color thing, but averaging column A is done by typing "=average(A:A)" or "=avg(A:A)" (can't remember which one office 2000 uses) in a cell. You could also just type "=average(" and click on the appropriate column. don't forget to close the parentheses.

Insert->Function->average can do this as well.

 

[Dilbert]

Being just a mere graphic designer, I'm not too farmiliar with Exel... but here it goes anyhow, hoping someone will be able to help me out!

1. I have bunch of numbers varing from 60-99 in hundreds of different cells in many rows and columns. I would like put heirachy in those numbers, so that 70s in yellow, 80s in orange, and 90s in red. Is there any way to do so without changing the color of the number or cell color manually?

2. I also would like to know how to average the numbers in one column.

I'm using Office 2000 on 2000 based PC - any help would be greatly appreciated!

lachesis

1) On the Format menu, choose Conditional Formatting. You can enter up to three criteria which evaluate to "true" or "false." If you have more than three criteria, I'm not sure what you'd do.

2) Use the AVERAGE function. In a new cell, enter =AVERAGE(argument 1, argument 2, ...) and it will calculate the arithmetic mean.

For example, if the column of numbers starts in cell B5 and ends in cell B42, the function would be =AVERAGE(B5:B42).

 

[cubicle47b]

I don't think there's anything built into Excel for #1 (although I haven't checked). I'd write a VBA macro that checks the cells within a range for those values and color codes them for you. It's not hard at all, it would probably take 10 minutes if you knew what you were doing.

 

[cubicle47b]

There, I was bored -

Sub Test()

'variables

Dim Temp_X As Integer
Temp_X = 1

Dim Temp_Y As Integer
Temp_Y = 1

Dim X_Coordinate As Integer
X_Coordinate = 50

Dim Y_Coordinate As Integer
Y_Coordinate = 50


'code

'loop through cells
While Temp_X <= X_Coordinate

Temp_Y = 1
While Temp_Y <= Y_Coordinate

'select current cell
Cells(Temp_X, Temp_Y).Select
With Selection.Interior
If ActiveCell.FormulaR1C1 >= 70 And ActiveCell.FormulaR1C1 <= 79 Then
'yellow
.ColorIndex = 6
ElseIf ActiveCell.FormulaR1C1 >= 80 And ActiveCell.FormulaR1C1 <= 89 Then
'orange
.ColorIndex = 46
ElseIf ActiveCell.FormulaR1C1 >= 90 And ActiveCell.FormulaR1C1 <= 99 Then
'red
.ColorIndex = 3
End If
End With

Temp_Y = Temp_Y + 1

Wend

Temp_X = Temp_X + 1

Wend

'reset coordinates
Cells(1, 1).Select

End Sub


PM me and I'll send you the Excel file.

 

[lachesis]

Thank you very much all! Cubicle, please check your PM as well.

lachesis