MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I am trying to enter a formula within excel that returns a value if two conditions, each in separate cell, are met.
So if the number in cell A1 ends with a 3
And the text in cell A2 is "Remain"
I want cell A3 to say "Yes".
If neither condition is met i want it to say "no"
And if A1 & A2 are blank, A3 to be blank (rather than #VALUE)
So far I can get a yes or no for the first part using
(IF(MOD(VALUE(RIGHT(I11,1)),3)=0,"Yes","No"))
But I'm having trouble linking in the A2 conditions.
Any experts out there that can help?
Replace the "Yes" with a corresponding IF statement which tests the other cell so you have the IF statements nested.
=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",IF(AND(RIGHT(A1,1)="3",B1="Remain"),"Yes","No"))
if the number in cell A1 ends with a 3
Not my forte and I've only glanced at it, but isn't what you've there testing whether it's [i]divisible[/i] by 3 rather than ending in 3?
Difficult to actually return a blank cell (certainly beyond my knowledge) but returning a 0 or logical is easy enough...
=if(or(isblank(a1),isblank(a2)),0,if(and(right(a1,1,1)=3,a2="remain"),"yes","no"))
If you need the cell to then appear blank use conditional formatting to "hide" the 0s
Edit - Or as above you could replace the zero with "" which would be sort of blank
divisible by 3
Sure there is actually a function for this but I'd do that long hand with =if(a1/3=int(a1/3),true,false)
or in the above replace the right function with the above
Matt-P and dangerousbrain - that works. Thank you very much
I see what you did there and it's a good job I asked. I was a long way down the wrong road wasn't I.
Anyway, thanks again