I cannot work out how to formulate my IF formula in my spreadsheet

Kaelkitty1 · April 23, 2022, 4:57am

Hi, I’m 63 years old so my Math is VERY rusty. I have a column of 457 currency amounts in my spreadsheet - some of them are positive and the others are negative. I need to separate them so that I have one column of positive numbers and one of negative numbers. All I get no matter what I try is either error 522 (circular reference) or error 510 (missing variable) and I am just about to tear my hair out!

What I want to do is “look” at the number in Column C; put it in Column D if it is a negative number, or put it into Column E if it is a positive number, and to do this for all 457 numbers.

I can’t remember all the combinations I have tried. but the last one I tried was IF(C1<0,D1=C1,E1=C1) and the formula wizard made that into:

Formula

If anyone can help I would truly appreciate it,

Yours Sincerely,
Kaelkitty.

JohnSUN · April 23, 2022, 5:15am

Welcome!

You just need two separate formulas for each of the target columns, one for column D =IF(C1<0;C1;"") and one for column E =IF(C1>=0;C1;"").

The formulas are very similar, but they are different, differing only in the condition.

Kaelkitty1 · April 23, 2022, 5:18am

THANK YOU! I Was putting my formula in the wrong column, to start with! (Column C)! Some times I just tie myself in knots!

Yours Sincerely,
Kaelkitty.