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","comment":[{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/51953220","author":{"@type":"Person","name":"lyesbkz"},"datePublished":"2024-01-10T18:33:15.000Z","text":"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"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/51953056","author":{"@type":"Person","name":"one_eight_seven"},"datePublished":"2024-01-10T18:11:48.000Z","text":"Are people posting their (home)work on here now?! "},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/51953122","author":{"@type":"Person","name":"iCrazyCarrots"},"datePublished":"2024-01-10T18:21:54.000Z","text":"Look at conditional formatting….."},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/51953157","author":{"@type":"Person","name":"cecilmcroberts"},"datePublished":"2024-01-10T18:25:32.000Z","text":"ablebits.com/off…ts/"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/51953254","author":{"@type":"Person","name":"luv_a_deal"},"datePublished":"2024-01-10T18:37:45.000Z","text":"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"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/51953718","author":{"@type":"Person","name":"jameshothothot"},"datePublished":"2024-01-10T19:38:01.000Z","text":"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: