Hello
I have a list of numbers in a "Data" column and a column to the right of it that divides the LOG of that value by the SUM of the LOG of each value in the entire Data column:
=LOG([@Data])/SUM(LOG([Data]))
This works fine as long as there are no values in the [Data] column with a value of "0". If there are, this results in the entire second column displaying "#NUM!". The error is expected and caused by "LOG(0)". I simply need a way to substitute "LOG(0)" for "0" for any zero Data value rows so that the formula still results in giving me calculation values for any non-zero Data values instead of "#NUM!".
My current workaround requires two calculation columns instead of one. This is shown in the table on the right. First, the [LOG] column calculates the LOG of each Data value that is not equal to zero. If the Data value is a zero, it puts a 0 in the column. The second column then does the job of dividing the current row's [LOG] column value by the SUM of the [LOG] column values
Just looking to see if anyone knows a solution that can be done in a single calculation column - some kind of merge of the formulas I have in the two columns in the right-most table? I think It would still need to incorporate the IF statement to handle zero Data values, but also have have some kind of inbuilt expanding total to the SUM part of the calculation. I just can't work out how to do it, or if it's even possible. Thanks in advance.
I have a list of numbers in a "Data" column and a column to the right of it that divides the LOG of that value by the SUM of the LOG of each value in the entire Data column:
=LOG([@Data])/SUM(LOG([Data]))
This works fine as long as there are no values in the [Data] column with a value of "0". If there are, this results in the entire second column displaying "#NUM!". The error is expected and caused by "LOG(0)". I simply need a way to substitute "LOG(0)" for "0" for any zero Data value rows so that the formula still results in giving me calculation values for any non-zero Data values instead of "#NUM!".
My current workaround requires two calculation columns instead of one. This is shown in the table on the right. First, the [LOG] column calculates the LOG of each Data value that is not equal to zero. If the Data value is a zero, it puts a 0 in the column. The second column then does the job of dividing the current row's [LOG] column value by the SUM of the [LOG] column values
Just looking to see if anyone knows a solution that can be done in a single calculation column - some kind of merge of the formulas I have in the two columns in the right-most table? I think It would still need to incorporate the IF statement to handle zero Data values, but also have have some kind of inbuilt expanding total to the SUM part of the calculation. I just can't work out how to do it, or if it's even possible. Thanks in advance.
| Log.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ||||||||
| 2 | Workaround to prevent "#NUM!" when any Data values are "0" | |||||||
| 3 | Data | Log of Data / SUM of Log of Data | Log | Log of Data / SUM of Log of Data | ||||
| 4 | 323902 | 0.0820 | 5.51041363 | 0.0820 | ||||
| 5 | 423434 | 0.0838 | 5.626785727 | 0.0838 | ||||
| 6 | 3686458 | 0.0977 | 6.56660929 | 0.0977 | ||||
| 7 | 27143943 | 0.1107 | 7.433672935 | 0.1107 | ||||
| 8 | 96219658 | 0.1188 | 7.983263809 | 0.1188 | ||||
| 9 | 14878303868 | 0.1514 | 10.17255342 | 0.1514 | ||||
| 10 | 30817024 | 0.1115 | 7.488790697 | 0.1115 | ||||
| 11 | 84471023 | 0.1180 | 7.926707754 | 0.1180 | ||||
| 12 | 294824753 | 0.1261 | 8.469563943 | 0.1261 | ||||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E4:E12 | E4 | =IF(Table1[@Data]=0,0,LOG(Table1[@Data])) |
| F4:F12 | F4 | =[@Log]/SUM([Log]) |
| C4:C12 | C4 | =LOG([@Data])/SUM(LOG([Data])) |
That's exactly it. I was getting something close to that, but not good enough!
