I have the following formula that is functioning correctly:
OFFSET(B2,MATCH(LARGE(OFFSET(B3,0,MATCH(A1,B2:AF2,0)-1,1000,1),1),OFFSET(B2,0,MATCH(A1,B2:AF2,0)-1,1000,1),0)-3,MATCH(A1,B2:AF2,0)-1)
The formula basically finds the date in A1 in the range B2:AF2, and then finds the largest number in that column and returns the cell 2 rows above.
I need to expand on it and say if any of the cells in A3:A1001 contain the word "account" do not use the value found in that row to determing the LARGE number. The labels will be in the format account1, account2, etc..
Any suggestions?
I think only a parent could love this formula - it's a bit hard to work out. My suggestion is to wrap everything you have got into an if statement
Something like this
=if(condition,Do if true, do if false) therefore
=if(Account exists Y/N,Take the account answer,take the formula answer)(
What does contains account mean? If it just contains the word account, so not "account 1234"
Perhaps the following entered as an array Sht ctrl then enter
OFFSET(B2,MATCH(LARGE((OFFSET(B3,0,MATCH(A1,B2:AF2, 0)-1,1000,1),1)*(NOT(A3:A1002="account"))),OFFSET(B2,0,MATCH(A1,B2:AF2,0)-1,1000,1),0)-3,MATCH(A1,B2:AF2,0)-1)
I hope thats correct
In the sheet I sent you last time, I have amended it and it is in cell k6 and made it look 2 rows above. Hopefully you can adjust it for your needs, but I have not the time to check if the formula typed above works as I would have to recreate your sheet. but could with the one I created before, hence the attachment. As the previous poster said it is a bit hard to work out!
Hope you get there, let me know
regards
Dav
Last edited by Dav; 11-01-2006 at 06:55 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks