0

I would like to perform an if statement with conditions on the values of multiple non-contiguous cells, something like

=IF(K2=O2=U2=AA2=AG2=AM2=AS2=AY2=BE2=BK2=BQ2=BW2=CC2=CI2=CO2=CU2=DA2=DG2=DM2=DS2=DY2=EE2=EK2=EN2=ER2=EV2=EZ2=FD2=0,"Pass","Fail")

However, this does not work. Is what I am trying to achieve possible?

I can't sum the cells because they contain positives and negatives and are potentially offsetting of each other, and I can't take the absolute values of the numbers in the array because the cells are non-contiguous.

I need specifically to check that each cell has value exactly zero in order to award a pass.

Any ideas?

Improve this question

4 Answers 4

Reset to default
2

You could use a hidden row somewhere else on your sheet to do your calculations.

Hold down the Ctrl and select each cell you want to check. Right click and choose copy from the menu. Go to the row you want to use, click in the first cell, press Ctrl+Alt+V, then select the "paste links" button at the bottom of the window. This should show all the values side-by-side.

Now use an IF statement (like the one below) to check if all values are 0. Note: where the A20:B20 is, will need to be the range of the row you just created.

IF(SUM(A20:B20)=0, "Pass", "Fail")
Improve this answer
3
  • Can you just confirm the Ctrl + N command as this creates a new workbook on my computer?
    – Aaa
    Oct 16, 2013 at 11:46
  • Sorry about that. I've updated the answer with the correct key combination.
    – CharlieRB
    Oct 16, 2013 at 11:54
  • Great. If this works for you, feel free to choose it as your answer.
    – CharlieRB
    Oct 16, 2013 at 15:22
1

The Zero is your friend and ally here. If you sum up the absolute values, you will get something above Zero if a single one of your values is not zero. Negative ones will end up being positive because of the ABS function and thus not mess with your sum. Like this:

=IF((ABS(K2)+ABS(O2)+....+ABS(CC2))=0,"Pass","Fail)
Improve this answer
2
  • Thanks. Is there a way of doing this more concisely? E.g. applying ABS once to the entire range even though the cells are non-contiguous?
    – Aaa
    Oct 16, 2013 at 9:57
  • I doubt it. VBA might be the better approach there. Then you can simply have a list with cell references and loop through checking them for being zero.
    – TheUser1024
    Oct 16, 2013 at 10:12
0

As your range isn't contiguous at first you need to name it to refer to it easily: select all the cells in the range, than name it (guidance on named range is e.g. here).
Then there are a couple of functions you can use:

  • =IF(COUNTIF(yourRange,0)=COUNTA(yourRange),"pass","fail")
  • =IF(SUMPRODUCT(yourRange,yourRange)=0,"pass","fail")
  • =IF(SUM(ABS(yourRange))=0,"pass","fail") - this is an array formula, you need to enter it by CTRL+SHIFT+ENTER
  • =IF(SUM(yourRange)=0,"pass","fail") - all the other formulas work for all numbers, this works only if you expect only non-negative values in cells.
Improve this answer
0

Provided that none of the other cells in row 2 have the value 0, and you have 28 cells to check, you can use

=IF(COUNTIFS(2:2, "=0")=28,"pass","fail")

This will ignore text in other fields in row 2, and if any of the numbers is non-zero or blank, it will give a fail.

If you have more or less than 28 values to check, then change this number accordingly.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.