Displaying a Value Based on the Contents of Two Other Cells

In P20 I want to display the contents of U20 if J20 and M20 are not 0.00 or empty.
In P21 I want to display the contents of U21 if J21 and M21 are not 0.00 or empty.
In P22 I want to display the contents of U22 if J22 and M22 are not 0.00 or empty.

Spreadsheet attached
InvoiceTest1.xls
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

try

=IF(SUM(J20,M20)>0,U20,"")

copy down.  The screenshot shows the formula that has been entered in row 20 and copied down to row 22.

screenshot


cheers, teylyn
0
savic7ukCommented:
Hi,

Try this if you want either J20 or M20 condition  to be valid
=IF(OR(OR($J20>0;(NOT(ISBLANK($J20))));(OR($M20>0;(NOT(ISBLANK($M20))))));$U20;"")

And Try this if you want both conditions to be valid at the same time
=IF(AND(OR($J20>0;(NOT(ISBLANK($J20))));(OR($M20>0;(NOT(ISBLANK($M20))))));$U20;"")

Hope it helps

Untitled.png
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
savic7uk,

I think you make it more complicated than it needs to be.  The file is set up as a statement sheet, where charges are added and credits are subtracted from the balance. When the sum of the two contains a value that is greater than 0 then at least one of the cells has a number. If the sum is 0, the cells may contain text.

Hence my suggestion with Sum().

Testing whether the cell is blank contributes nothing to the end result and can be omitted

If you want to be really picky you could argue that the cells could contain a negative value. In that case, evaluating the data type with Isnumber() can be paired with a comparison to zero like this:

=IF(OR(AND(ISNUMBER(J20),J20<>0),AND(ISNUMBER(M20),M20<>0)),U20,"")

... which is still a lot shorter and less convoluted.

In the end, let's take a look at the data architecture and the expected result. Data validation can be set up do prevent negative values. The formula can be really simple.

=IF(SUM(J20,M20)>0,U20,"")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

savic7ukCommented:
Hi teylyn

That is your opinion which you already stated, i went for an alternative solution, one i believe is the correct and which covers the requirements set in the question and of course it is what i would have done.

No point in arguing really.

If what i proposed was wrong, then of course you should comment and correct me because the point of this is to provide solutions and not to point to the wrong directions. Anyways is up to the questioner to decide which solution suits him.

Have a nice day
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Although the second column is a credit column and the simple arithmetic to the sheet is

Previous line's Column 3 + Current line's Column 1 - Current line's Column 2 = Current line's Column 3

You could have a negative amount in Column 2.  When that happens nothing was displayed.

So I changed     =IF(SUM(J20,M20)>0,U20,"")

to                       =IF(SUM(J20,M20)<>0,U20,"")  Which seems to take care of the problem.  

I haven't tested savic7uk's suggestion yet.
0
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for teylyn's comment #a40905508
Assisted answer: 0 points for Bill Golden's comment #a40905883

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.