If nested
- Thread starter bines53
- Start date
Somendra Misra
Excel Ninja
Hi Bines53,
Try this:
=IF((SUMPRODUCT(--(MOD(A10:B10,2)=0)))=2,1,IF(SUMPRODUCT(--(MOD(A10:B10,2)))=2,2,IF((A10>B10),3,4)))
Regards,
Try this:
=IF((SUMPRODUCT(--(MOD(A10:B10,2)=0)))=2,1,IF(SUMPRODUCT(--(MOD(A10:B10,2)))=2,2,IF((A10>B10),3,4)))
Regards,
Somendra Misra
Excel Ninja
Welcome Back anytime.
Regards,
Regards,
NARAYANK991
Excel Ninja
Hi David ,
As far as I can see , your formula differs from Misra's only in the parentheses ! You have one in the wrong place , and you don't have two of them where they are needed. The problem is that when a formula is being developed , we tend to think in terms of Excel formulae , when we should be thinking in terms of the logic required to solve the problem ; I do not know where and why you are using the logic that you say is correct , but from what I have understood , it is :
1. If both numbers are EVEN - Action 1
2. If both numbers are ODD - Action 2
3. If number in column A is greater than the number in column B - Action 3
4. Or else - Action 4
Clearly , the rules are in order of priority , so that if a cell in column A has the number 11 , and the corresponding cell in column B has the number 99 , it is rule 2 which will decide the outcome , and not rule 4.
If the above logic is correct , then the first draft of a formula would be :
=IF(AND(ISEVEN(A10),ISEVEN(B10)),1,IF(AND(ISODD(A10),ISODD(B10)),2,IF(A10>B10,3,4)))
The above formula may appear to be very simple and not 'worthy' of an expert in Excel , but the point is that when a problem needs a solution , that solution should be the simplest one possible , something which is easily verified , and something which can be explained even to a layman , provided of course that the subject itself is explainable ; I don't think a subject like Relativity can be explained in layman's language , but the subject in question is itself quite simple ; introducing things like SUMPRODUCT and MOD and CHOOSE looks good , but I doubt if it adds value.
Narayan
As far as I can see , your formula differs from Misra's only in the parentheses ! You have one in the wrong place , and you don't have two of them where they are needed. The problem is that when a formula is being developed , we tend to think in terms of Excel formulae , when we should be thinking in terms of the logic required to solve the problem ; I do not know where and why you are using the logic that you say is correct , but from what I have understood , it is :
1. If both numbers are EVEN - Action 1
2. If both numbers are ODD - Action 2
3. If number in column A is greater than the number in column B - Action 3
4. Or else - Action 4
Clearly , the rules are in order of priority , so that if a cell in column A has the number 11 , and the corresponding cell in column B has the number 99 , it is rule 2 which will decide the outcome , and not rule 4.
If the above logic is correct , then the first draft of a formula would be :
=IF(AND(ISEVEN(A10),ISEVEN(B10)),1,IF(AND(ISODD(A10),ISODD(B10)),2,IF(A10>B10,3,4)))
The above formula may appear to be very simple and not 'worthy' of an expert in Excel , but the point is that when a problem needs a solution , that solution should be the simplest one possible , something which is easily verified , and something which can be explained even to a layman , provided of course that the subject itself is explainable ; I don't think a subject like Relativity can be explained in layman's language , but the subject in question is itself quite simple ; introducing things like SUMPRODUCT and MOD and CHOOSE looks good , but I doubt if it adds value.
Narayan
Hi all,
Another question on the same subject,
I have a lot if nested, is there another way, short and effective.
There are eight numbers, 4 small (7 8 9 10) and 4 large (11 12 13 14)
What I did, I built all the possibilities for comparison of two big numbers against two small numbers in each row, there are six types of comparison.
Thank you !
Another question on the same subject,
I have a lot if nested, is there another way, short and effective.
There are eight numbers, 4 small (7 8 9 10) and 4 large (11 12 13 14)
What I did, I built all the possibilities for comparison of two big numbers against two small numbers in each row, there are six types of comparison.
Thank you !
Somendra Misra
Excel Ninja
Hi Bines,
Do you just want to shorter your formula but result should be tha complete column?
I mean what exactly are you looking for?
Regards,
Do you just want to shorter your formula but result should be tha complete column?
I mean what exactly are you looking for?
Regards,
Hi Somendra ,
I added a new file with the maximum 16 possible options, I need a formula that would give a suitable combination per line and a line from the 16 options, for example, in column G I made up six options of 16 options.
If this is not possible, an abbreviated version of the formula in column G.
Thank you !
I added a new file with the maximum 16 possible options, I need a formula that would give a suitable combination per line and a line from the 16 options, for example, in column G I made up six options of 16 options.
If this is not possible, an abbreviated version of the formula in column G.
Thank you !
Debraj
Excel Ninja
HI David,
Dont.. worry.. believe me we love to solve as much as unique queries.. and specially your one.. very hard to understand.. and need a real logic & calculation skill..
I am just making is lil bit fun.. but really appreciate your question.. coz its really need a lot of mind atleast to understand your requirement and to avoid OFFSET..
Right now.. its not fully matching with your provided output, but I still tried my first draft.. as I assume.. their may be some manual typo mistake in your provided answer..
Check it.. Please dotn mind.. this is just for FUN.. no personal attack..
=MATCH(4,MMULT(--($L$3:$O$18=LOOKUP($A3:$D3,{7,"SMALL";10,"BIG"})),{1;1;1;1}),0)
Lil bit change in the lookup Formula.. and No CSE.. required..
=MATCH(4,MMULT(--($L$3:$O$18=LOOKUP($A3:$D3,{7,"SMALL";11,"BIG"})),{1;1;1;1}),0)
Dont.. worry.. believe me we love to solve as much as unique queries.. and specially your one.. very hard to understand.. and need a real logic & calculation skill..
I am just making is lil bit fun.. but really appreciate your question.. coz its really need a lot of mind atleast to understand your requirement and to avoid OFFSET..
Right now.. its not fully matching with your provided output, but I still tried my first draft.. as I assume.. their may be some manual typo mistake in your provided answer..
Check it.. Please dotn mind.. this is just for FUN.. no personal attack..
Lil bit change in the lookup Formula.. and No CSE.. required..
=MATCH(4,MMULT(--($L$3:$O$18=LOOKUP($A3:$D3,{7,"SMALL";11,"BIG"})),{1;1;1;1}),0)
Last edited:
Somendra Misra
Excel Ninja
Hi Debraj/Bines,
Correct me If I am wrong.
You use the L3:O18 array to get result, but I think they are just few assumptions and there can be a lot more combinations. I still don't understand what Bines is actually looking for???? If he wants to get a combination number than there has to be a standard set of combinations because if he desingnate BIG,SMALL,BIG,BIG as 1 I could have taken it 2 and so on....
So I am still Confused...
Regards,
Name Edited.
Correct me If I am wrong.
You use the L3:O18 array to get result, but I think they are just few assumptions and there can be a lot more combinations. I still don't understand what Bines is actually looking for???? If he wants to get a combination number than there has to be a standard set of combinations because if he desingnate BIG,SMALL,BIG,BIG as 1 I could have taken it 2 and so on....
So I am still Confused...
Regards,
Name Edited.