Copy column if value is number

[ElisabethC]

Hi,

I would like to compare column A and column B and calculate how much % B is of A.
So I added column C with formula "=B/A"
However some of the values in A and B are 0, which result in a statement "Not a number" in column C.
Unfortunately pivot tables can't handle this as a value.

So I tried to add column D that would copy C's value and would set 0 instead of "Not a number".
I used this formula, but it seems to be incorrect:
=IF([column C]="NaN (Not a number)";"0";"column C]")

Can anybody help with the formula? Or maybe I don't need column D and solve my base problem in another way?

Thanks!

 

[Joe4]

Welcome to the Board!

Since you cannot divide by zero, when column A is zero, that would cause an error or problem.
So try entering this for your formula in column C (this example assuming row 2), which will return a zero whenever column A is zero (instead of an error or message):

=IF(A2=0,0,B2/A2)

 

[MarcelBeug]

If this is happening in Power Query (as this s the Power BI forum), then you can use

=if Number.IsNaN([Column C]) then 0 else [Column C]

 

[ElisabethC]

Thanks for the help! That's exactly what I was looking for!

Hi,

I would like to compare column A and column B and calculate how much % B is of A.
So I added column C with formula "=B/A"
However some of the values in A and B are 0, which result in a statement "Not a number" in column C.
Unfortunately pivot tables can't handle this as a value.

So I tried to add column D that would copy C's value and would set 0 instead of "Not a number".
I used this formula, but it seems to be incorrect:
=IF([column C]="NaN (Not a number)";"0";"column C]")

Can anybody help with the formula? Or maybe I don't need column D and solve my base problem in another way?

Thanks!

 

[Joe4]

My mistake. Didn't realize I was in the Power BI forum.