Home Forum Chat Forum Excel formula help

Viewing 7 posts - 1 through 7 (of 7 total)
• Excel formula help
• allthepies
Member

Replace the “Yes” with a corresponding IF statement which tests the other cell so you have the IF statements nested.

Matt-P
Member

=IF(OR(ISBLANK(A1),ISBLANK(B1)),””,IF(AND(RIGHT(A1,1)=”3″,B1=”Remain”),”Yes”,”No”))

Cougar
Subscriber

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 divisible by 3 rather than ending in 3?

dangeourbrain
Subscriber

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

dangeourbrain
Subscriber

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

silverpigeon
Member

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

silverpigeon
Member

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?

Viewing 7 posts - 1 through 7 (of 7 total)

The topic ‘Excel formula help’ is closed to new replies.