Displaying IF statement result in another cell...

dariuzthepole

Board Regular
Hi All,

Is there a way to reference what decision has been made by an IF statement in another cell? E.g. IF statement is in cell A1. In cell A2, I want to display whether the IF statement has chosed the TRUE value of the FALSE value.

Can this be done? Any help would be greatly appreciated.

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

PATSYS

Well-known Member
What is the exact formula you have in A1?

dariuzthepole

Board Regular
Strictly speaking it isn't in cell A1, but this is what I have...

=IF(Q23-(Q25+Q26)<0,(Q23-(Q25+Q26))*(-1),(Q23-(Q25+Q26)))

...for budget purposes, if the calculation produces a negative number, I want to display "Deficit" in the adjacent cell. At the same time, I don't want a negative number showing as a deficit (hence why the TRUE value in the above equation is multiplied by -1).

The above formula works perfectly. In the adjacent cell, I want to display either "Deficit" or "Surplus". I have a similar equation to the above...

=IF(K32>0,"Surplus","Deficit")

...but because the original formula converts all negative numbers to positive, it always displays "Surplus"!

lenze

Legend
For your first formula, why not just use
Code:
``=ABS((Q23-(Q25+Q26))``
. For the second, use
Code:
``=IF(Q23-(Q25+Q26)<0,"Deficit","Surplus")``

lenze

Well-known Member
you could always go into the formatting and remove the - sign from there and display your negatives as red tx but with no sign?

dariuzthepole

Board Regular
For your first formula, why not just use
Code:
``=ABS((Q23-(Q25+Q26))``
. For the second, use
Code:
``=IF(Q23-(Q25+Q26)<0,"Deficit","Surplus")``

lenze

This solves half of the problem. However, if the calculation brings a negative number it will still say "Surplus", as the negative element is disgarded using the ABS function.

lenze

Legend
However, if the calculation brings a negative number it will still say "Surplus", as the negative element is disgarded using the ABS function.
Are you sure? My second formula doesn't use ABS
lenze

dariuzthepole

Board Regular
Are you sure? My second formula doesn't use ABS
lenze

I'm wrong. I was referencing the cell which had the ABS function (which is obviously positive), instead of doing the calculation in the IF statement.

Thanks!

Well-known Member
The 2nd part of lenze's statement does the whole IF, so your going to get either a surplus or a deficit based on that and not based on what's displaying in the first part. the first part is seperate. You need to run both

Replies
1
Views
124
Replies
1
Views
187
Replies
4
Views
314
Replies
0
Views
195
Replies
2
Views
607

1,190,918
Messages
5,983,578
Members
439,852
Latest member
balasat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back