How do I rank numbers and skip cells that contain just formula but not the calculated value

[shizu]

I want to rank numbers in a range that contains the following formula "=SUM(E2,F2,G2,H2)" using the RANK function "=Rank(I2,$I$2:$i$200)" but I want to skip cells that contain just the formula and no calculated value, until the calculated value is in the cell then that cell can be ranked. Can someone help?

 

[VBA Geek]

I do not understand the part "I want to skip cells that contain just the formula and no calculated value, until the calculated value is in the cell then that cell can be ranked"

maybe an example?

 

[shizu]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]1c (E)[/TD]
[TD]2c (F)[/TD]
[TD]3c (G)[/TD]
[TD]Ex (H)[/TD]
[TD]Total (I)[/TD]
[TD]Ranking[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]40[/TD]
[TD]64[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]30[/TD]
[TD]51[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]60[/TD]
[TD]72[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

All cells under column heading Total contains the formula "=SUM(E2F2G2H2) to =SUM(Enth,Fnth,Gnth,Hnth)", All cells under column heading Ranking contains the formula "=Rank(I2,$I$2:$i$200) to =RANK(Inth,$I$2:$i$200)". Now I don't want cells under the column heading Ranking to be showing the next rank position for cells under the column heading Total with no calculated value

 

[Peter_SSs]

Like this?

(Note the simpler SUM formula too)

Excel Workbook
	E	F	G	H	I	J
2	8	8	8	40	64	2
3	7	7	7	30	51	3
4	4	4	4	60	72	1
5					0
6					0
7					0
Rank

 

[shizu]

That you very much Peter_SSs, that worked. I have tried "=IF(I2="","",RANK(I2,I$2:I$200))" and could not figure out why it wouldn't solve the issue, now I know why thank you.

 

[Peter_SSs]

I have tried "=IF(I2="","",RANK(I2,I$2:I$200))"

That might be a neater solution if you do that and change your SUM formula as shown.

Excel Workbook
	E	F	G	H	I	J
2	8	8	8	40	64	2
3	7	7	7	30	51	3
4	4	4	4	60	72	1
5
6
7
Rank (2)