Excel formula help

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”))

    Premier Icon 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?

    Premier Icon 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

    Premier Icon 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.