Can I do an IF function to find the value if it is greater than 0 in more than 8 rows?

| A | B
1 | 0 |
2 | 0 |
3 | 0 |
4 | 4 |
5 | 0 |
6 | 0 |
7 | 0 |
8 | 0 |

=IF(A1>0,A1,IF(A2>0,A2,IF(A3>0,A3,IF(A4>0,A4,IF(A5 >0,A5,IF(A6>0,A6,IF(A7>0,A7,IF(A8>0,A8))))))))

RESULT=4

But I got an error message after A9 or row9.

Any help would be appreciated.

Thanks.

retsoksirhc

At the end, shouldn't it be IF(A8>0,A8,0)<with lots more )'s.>

Otherwise, you're missing an argument...you want that cell to show 0 if everything else is 0, right?

retsoksirhc

Oh...I think I get what you're doing now...

If that first post of mine helps, great. If not, then I was thinking you wanted somthing else entirely...sorry.

JBeaucaire

It would be easier if we knew what you were trying to accomplish. IF statements can't go on forever, which is why arrays and lookup tables are so useful depending on your actual goal.

Care to elaborate? Describe your situation in non-excel terms.

mdosh01

I'd need more information. First, you are evaluating whether the cells in column A are greater than 0. In this case they all are since column A contains the values 1 through 8. Assuming you meant to evaluate column B, I'm not sure what you want returned. Do you want a row value returned "4". Or do you want the value in B4 returned "4".

The following formula will evaluate column b and return the row number. If you want the value, then substitute the hard numbers with the cell number with no quotes:

=IF(B1>0,"1",IF(B2>0,"2",IF(B3>0,"3",IF(B4>0,"4",I F(B5>0,"5",IF(B6>0,"6",IF(B7>0,"7",IF(B8>0,"8",0)) ))))))

However, this formula will stop at the first non-zero number. If you only expect to have one number to be non-zero in the range, then the MAX function might work.

A little more detail about what you need and I think we can figure this out.

Wonder84lalala

Hi,

I have four tabs (sheet1, sheet2, sheet3,result)

I want to get the selected value from one of the three tabs (sheet1-3) where the input value is greater than 0 to the result tab.

For example:

sheet1
Cell A |
1 | 0 |
2 | 0 |
3 | 0 |
4 | 4 |

sheet2
Cell A |
1 | 0 |
2 | 0 |
3 | 0 |
4 | 0 |

sheet3
Cell A |
1 | 0 |
2 | 0 |
3 | 0 |
4 | 0 |

result
4

In this case, sheet1 is selected from the first three tabs and the output is 4 in result tab.

Can I use other functions beside the IF function? Thanks.

ScottGem

what if more than one cell is greater than zero? It might help if you explain the purpose of this sheet.

Wonder84lalala

Sheet1, Sheet2, and Sheet3 are inputs from user, and all the cells are 0 as default. A user can only select one of three tabs. If he/she selects sheet2 and enter in 4 in one of the cells, then the result tab should show 4 from sheet2. Thanks again

ScottGem

OK, So only one of the three tabs will be greater than 0 and only one cell in that tab will have a value? If so, I would make it very easy. I would just sum the three ranges in your results sheet:

=SUM(Sheet1!B1:B4,Sheet2!B1:B4,Sheet3!B1:B4)

This would return a 4.

Wonder84lalala

If sheet2 is selected from one of the three tabs, can I have the results sheet to be: 50% from cell B of Sheet2?

sheet1
Cell A | B |
1 | 0 | 10% |
2 | 0 | 50% |
3 | 0 | 30% |
4 | 0 | 70% |

sheet2
Cell A | B |
1 | 0 | 20% |
2 | 0 | 30% |
3 | 0 | 45% |
4 | 4 | 50% |

sheet3
Cell A | B |
1 | 0 | 40% |
2 | 0 | 20% |
3 | 0 | 30% |
4 | 0 | 60% |

Result: 50%

Thanks for your helps.