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.

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?

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 something else entirely... sorry.

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.

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",IF(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.

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.

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

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 4 in one of the cells, then the result tab should show 4 from sheet2. Thanks again :D

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.

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.

See this is why we asked you to detail what you are trying to accomplish. The more you explain your app the better we can help you.

What you are now asking is different then what you previously asked. What you really want is to show the selected percentage from the selected sheet. And this is much more complex then your original request.

Let me play with this and see if I can come up with something.

This is what I came up with. It only uses 1 IF statement per sheet, and will find the FIRST entry >0 in column A starting on sheet1 and work forward. When it finds any entry > 0 it will report back the associated percentage value to your results cell. Be sure to format the results cell to display percentages.


=IF(SUM(Sheet1!$A$1:$A$4)>0,VLOOKUP(SUM(Sheet1!$A$1:$A$4),Sheet1!$A$1:$B$4,2 ),
IF(SUM(Sheet2!$A$1:$A$4)>0,VLOOKUP(SUM(Sheet2!$A$1:$A$4),Sheet2!$A$1:$B$4,2 ),
IF(SUM(Sheet3!$A$1:$A$4)>0,VLOOKUP(SUM(Sheet3!$A$1:$A$4),Sheet3!$A$1:$B$4,2 ))))

This can be made to look even cleaner codewise if you were to insert some "names" in your sheet to make it clearer what each set of numbers represented.

We're still guessing what you are trying to accomplish here. The lookup formula for an unsorted list, which I assume we could have here, is:

=INDEX(A1:B4,MATCH(4,A1:A4,0),2)

This will find the value "4" in column A and return the value found in the same row for column B.

If the value just has to be greater than 0 and there will only be one value greater than 0, then the formula becomes:

=INDEX(A1:B4,MATCH(MAX(A1:A4),A1:A4,0),2)

Max will return the one value that is greater than 0 which is then used to do the match.

At this point, I don't know if you care which sheet has the value greater than zero. If you do, you can use the following:

=IF(SUM(Sheet1!A1:A4)>0,"sheet1",IF(SUM(Sheet2!A1:A4)>0,"Sheet2",IF(SUM(Sheet3!A1:A4)>0,"Sheet3","None")))

You could then insert the formula above in place of "sheetx".

Be aware that if you use VLOOKUP, the list must be sorted. In this case, the data in column A would have to be in sort order.