I have the following which I am trying to combine into one cell:
If D3>0 and J3>0, "no change"
If D3=0 and J3=0, "no change"
If D3>0 and J3=0, "change"
If D3=0 and J3>0, "change"
In other words, I have four possbile scenarios with either change or no change being the answer. Hope that makes sense. Any help would be greatly appreciated.
would this work for you ...=IF(AND(D3>=0,J3>=0),"no change","change")
EDIT: never mind, needs rewritten.
Last edited by Sam Capricci; 01-29-2015 at 04:37 PM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Can you elaborate a little more...
If either cell is >0, could it be ANY number, like 5, or 30072 or 1 ??
Or are they basically 1 or 0 choices?
Will they both be the same number if they're >0 ?
And is to true to say that niether cell will ever be anything other than either 0 or >0? It will never be a negative number, or a text string, or blank?
If they might be blank, should the blank be considered the same as 0 ?
Perhaps you could post a sample book with your expected results.?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
ok, this should work for you, though I think there is a more efficient way to write it...
=IF(AND(D3>0,J3>0),"no change",IF(AND(D3=0,J3=0),"no change",IF(AND(D3>0,J3=0),"change",IF(AND(D3=0,J3>0),"change"))))
. . . . . . . . . . . . . .
@Sambo kid: you could use OR to combine some of the options. Depending on what values the cells can take, your code could return FALSE ... if for example, one of the values was negative.
This did work, thank you very much.
ok, this should work for you, though I think there is a more efficient way to write it...
=IF(AND(D3>0,J3>0),"no change",IF(AND(D3=0,J3=0),"no change",IF(AND(D3>0,J3=0),"change",IF(AND(D3=0,J3>0),"change"))))
Qu'est-ce que. . . . . . . . . . . . . .
And my response in post #3?
TMS, yours worked as well, thank you:
=IF(OR(AND(D3>0, J3>0),AND(D3=0, J3=0)), "no change",IF(OR(AND(D3>0, J3=0),AND(D3=0, J3>0)), "change","not specified"))
there may be a far simpler solution, pending the answers to questions in post #4
jonmo1:
either cell could be any number greater than zero and they could also potentially be the same number. basically, i just want to the result to look at the two cells in question and determine if they were same or different. same = anything greater than zero or both zero. different = one zero, the other a number greater than zero.
Actually, if you have two positive but different numbers, it will return "no change". Therefore it IS important to understand what values may be presented.
The first criterion causes this:which is met by, say 4 and 5 ... no changeFormula:Please Login or Register to view this content.
Regards, TMS
Try
=IF((D3>0)+(J3>0)=1,"Change","No Change")
Good but probably needs to be:Formula:Please Login or Register to view this content.
Edit, actually, no ... 5 and 5
Jonmo1: that indeed works, you definitely win for simplicity.
Why not justFormula:Please Login or Register to view this content.
because while one cell could = 5, the other could = 6. Those don't equal but for my purposes, they are "no change" since > 1.
ok, so we understand
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks