Why excel is taking into consideration empty cells?

[indygo]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]=2^a1/(2^a1+2^a2+2^a3)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



So I have this formula which also takes into consideration empty cells (a3) for future entries.
Formula is very simple but works only if I include non-empty cells to that equation.
As you can see above a3 is empty (I want excel to calculate it once I put another number there).
Correct answer to this should be 0.5 but somehow excel tells me its 0.444

2/(2+2) = 0.5

So to my understanding excel is using logic empty cell = 0 therefore 2^0 =1 which then indeed equals 0.444
is there a way to tell excel to treat empty cells as empty instead of 0?

 

[sheetspread]

One way:

Excel 2010
	A	B
1	2	0.5
2	2
3
Sheet4

Cell Formulas
Range	Formula
B1	=2^A1/SUMPRODUCT(2^A1:A3,SIGN(A1:A3))

 

[Fazza]

is that correct for negative values in column A ?

 

[Rick Rothstein]

is that correct for negative values in column A ?

You are correct... sheetspread's formula would return incorrect values if and value in A1 thru A3 was negative. My gut tells me that probably won't be any such negative values in which case the formula is fine; however, if there could be negative values, I guess throwing an ABS function call around the SIGN function call would solve that problem.

 

[indygo]

thank you