In Row 6, I am trying to have cell H6 divide cell the data in C6 by cell B6 unless there is data in cell D6 whereby I want it to ignore cell C6 and divide cell D6 by B6.....and so on thru cell F6, using the following nested function:
=IF(AND(C6>0,C6/B6), IF(D6>0,D6/B6), IF(E6>0,E6/B6), IF(F6>0,F6/B6))
The data in cell B6 is a constant and I've tried various iterations of this formula. This formula works up through cell D6; after that it only works for cell D6 regardless of what is in any other cell, including cell C6. Also when I added the piece referring to cell F6 I get the message that I have "to many arguments for this function." Have I discovered something that Excel can't do?
=IF(COUNTIF(C6:I6,">0")>0,B6/INDEX(C6:I6,MATCH(1,INDEX(--(C6:I6>0),1,),0)),"")
Regards
Bob
Hello Bob,
Thanks for your suggestion however, when I put a numbe in C6 it returns inaccurate information. For example the constant in B6 is 128, when I put 64 in C6, H6 displays 200%. It should display 50%; if I put 127 in C6, H6 displays 100.8%. It should display 99%. I tried reversing the B6/INDEX portion of the formula and replacing the other INDEX reference with B6 and got the #NAME? error. Also if I put a nimber in C6 and either D6, E6 or F6, H6 still only displays the results of C6. The only way it initiates the function in any cell besides C6 is if there is nothing in any other cell. Can the formula be structured to initiate the function based upon the last cell in the row, between C6 and F6, with out haveing to delete the data caontained in any other cell? Thanks for your help.
Dataman
To get that formula to work, reverse it like so
=IF(COUNTIF(C6:I6,">0")>0,INDEX(C6:I6,MATCH(1,INDEX(--(C6:I6>0),1,),0))/B6,"")
However, if you want the last value then use
=IF(COUNTIF(C6:F6,">0")>0,LOOKUP(2,1/(C6:F6>0),C6:F6)/B6,"")
Hey Bob,
It works, thanks a lot!!