Ask Experts Questions for FREE Help !
Ask
    Wonder84lalala's Avatar
    Wonder84lalala Posts: 41, Reputation: 1
    Junior Member
     
    #1

    Apr 24, 2008, 12:36 PM
    IF Functions > 0
    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's Avatar
    retsoksirhc Posts: 912, Reputation: 71
    Senior Member
     
    #2

    Apr 24, 2008, 12:52 PM
    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's Avatar
    retsoksirhc Posts: 912, Reputation: 71
    Senior Member
     
    #3

    Apr 24, 2008, 12:54 PM
    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Apr 24, 2008, 01:54 PM
    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's Avatar
    mdosh01 Posts: 64, Reputation: 8
    Junior Member
     
    #5

    Apr 25, 2008, 05:28 AM
    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's Avatar
    Wonder84lalala Posts: 41, Reputation: 1
    Junior Member
     
    #6

    Apr 25, 2008, 05:34 AM
    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #7

    Apr 25, 2008, 05:48 AM
    What if more than one cell is greater than zero? It might help if you explain the purpose of this sheet.
    Wonder84lalala's Avatar
    Wonder84lalala Posts: 41, Reputation: 1
    Junior Member
     
    #8

    Apr 25, 2008, 06:18 AM
    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
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    Apr 25, 2008, 06:37 AM
    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's Avatar
    Wonder84lalala Posts: 41, Reputation: 1
    Junior Member
     
    #10

    Apr 25, 2008, 06:52 AM
    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.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #11

    Apr 25, 2008, 07:02 AM
    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #12

    Apr 25, 2008, 11:24 AM
    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.
    Code:
    =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.
    mdosh01's Avatar
    mdosh01 Posts: 64, Reputation: 8
    Junior Member
     
    #13

    Apr 26, 2008, 07:46 AM
    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","No ne")))

    You could then insert the formula above in place of "sheetx".
    mdosh01's Avatar
    mdosh01 Posts: 64, Reputation: 8
    Junior Member
     
    #14

    Apr 26, 2008, 07:54 AM
    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.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Composition Functions and Inverse Functions [ 3 Answers ]

Wow! These things are boogers! :eek: any way to make these things easier?

Just functions [ 5 Answers ]

Hi there how would you solve this question algebratically: Find the range of this function: f(x)=1-3sin(2x), 0<x<Pi/2 First I multiply the restriction by 2 0<2x<Pi

Functions [ 12 Answers ]

(f(fx)) = 2x +4 when f(X) = 1... how do I solve this

Functions [ 2 Answers ]

I have a few questions concerning functions... 1) X^3+64 2) 54p^3-16q^3 3) 2x^2-5x=3 4) 2x^2-8x=0 :confused: :confused:


View more questions Search