Posted 10 January 2024

Excel help

Hi all.
Need help on an excel function please. Just can't think of what to use. see example below. I want to put a formula in e.g. a1, a2, a3 etc...I want a1 to return e.g. yes or 1 if there's anything in b1, c1, d1 etc. The data set is big so a nested if statement may take a while.

a b c d e
1 3 4
2 2
3 2 1
4 2

I'm good with excel but having a brain freeze!
thanks all
Community Updates
New Comment

17 Comments

sorted by
's avatar
  1. lyesbkz's avatar
    Assuming I've understood what you're trying to do.
    =COUNTA(B1:Z1)>0
    will return TRUE if there's anything in any of the cells from B1 to Z1 (can adjust columns as needed)
    Then fill the formula into other rows
    SP2013's avatar
    Author
    Magic...you sir/ or lady are a great person. Thank you
  2. one_eight_seven's avatar
    Are people posting their (home)work on here now?!
    SP2013's avatar
    Author
    Lol sorry definitely not. I used to work on excel for many years. Not used for over 2yrs!
  3. iCrazyCarrots's avatar
    Look at conditional formatting…..
    SP2013's avatar
    Author
    Thanks
  4. SP2013's avatar
    Author
    Thanks this helped too
  5. luv_a_deal's avatar
    COUNT (or COUNTA, if only looking for numbers) function? Could partner with a MIN if only want a 1 or 0? E.g. =MIN(1,COUNT(B1:n1)) - "n" being the last column you want to include.

    ETA: beaten to it... :), and originally stated MAX instead of MIN in error (edited)
    SP2013's avatar
    Author
    What does the max do? Not used this before...thanks
  6. jameshothothot's avatar
    try bing.com/new

    i asked it for you

    Hello, this is Bing. I’m glad you asked me for help.

    There are a few ways to check if a cell is blank or not in Excel. One of them is to use the IF function with the ISBLANK function. The ISBLANK function returns TRUE if a cell is empty and FALSE if not. For example, the formula in cell A1 would be:

    =IF(ISBLANK(B1:C1),"","Yes")

    This formula says IF (B1:C1 is blank, then return “”, otherwise return “Yes”). You can drag this formula down to fill the other cells in column A.

    Another way is to use the IF function with the <> operator. The <> operator means “not equal to”, so the expression B1:C1<>“” means B1:C1 is “not empty”. For example, the formula in cell A1 would be:

    =IF(B1:C1<>"","Yes","")

    This formula says IF (B1:C1 is not empty, then return “Yes”, otherwise return “”). You can also drag this formula down to fill the other cells in column A.

    Both formulas will give you the same result, as shown below:

    abcdeYes134Yes22Yes321Yes42You can find more information about these functions and other ways to check if a cell is blank or not in Excel from these web pages:

    I hope this helps. Have a nice day!
    SP2013's avatar
    Author
    Ahh brilliant. Thank you for this. I'll give it a try too.
    Have a good day
  7. oldskooladdict's avatar
    Blimey this takes me back, I used to ask stuff on the MrExcel forum eons ago. You'd post and then people would essentially fight it out between themselves to give you an answer which was *best*.. Was interesting to sit back and see something solved 5 ways!
's avatar