Why excel is taking into consideration empty cells?

indygo

Board Regular
Joined
Dec 2, 2013
Messages
117
ABC
12=2^a1/(2^a1+2^a2+2^a3)
22
3

<tbody>
</tbody>



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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
One way:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=2^A1/SUMPRODUCT(<font color="Blue">2^A1:A3,SIGN(<font color="Red">A1:A3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,827
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top