Array..I think??

[inkserious]

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?

 

[DirkStruan]

I think you need some VBA. Try the following:

Function Custom(InputDate As Date, DateRange As Range, LabelRange As Range)
For Each x In DateRange
    If x = InputDate Then
        c = x.Column
        Exit For
    End If
Next x
MaxNo = 0
For Each y In LabelRange
    If Not (y = "Account" Or y = "account") Then
        If MaxNo < Cells(y.Row, c) Then
            MaxNo = Cells(y.Row, c)
            targetrow = y.Row
        End If
    End If
Next y
Custom = Cells(targetrow, c)
End Function

It assumes the date range is horizontal and the label range is vertical (as per your description).


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[xlhelp]

If I am not mistaken, the last part above should be

Next y
Custom = Cells(targetrow-2, c)
End Function


Member- AAAA Association Against Acronym Abusers

 

[inkserious]

I guess I'm a little unsure where to start. The formula is actually on another sheet. I didn't include the sheet in the formula for simplicity. What I need to happen is this.

On Sheet1!D13, for example, I have the above formula that looks for the LARGE value on Sheet!2, based on the ranges described above, and returns the value in the cell one row above. In D14, I have the formula set to find the second largest value and return the value in the cell one row above, D15 the 3rd largest and so and so forth down for say the 20th largest value.

In E13, I have the formula set to find the LARGE value and return the cell value 2 rows above as opposed to 1 row.

In F13, the actual LARGE value; G13, the 1st row below.

I would prefer to use VBA as opposed to the quite ugly formula that I have above; however, my VBA is limited.

Thanks for any help.

 

[DirkStruan]

Xlhelp - Good spot, you're right.

Inkserious - ok, this is getting complicated. Given the complication I think it may be better to return to the worksheet functions but to break the function down into key stages in different cells.

If I understand correctly you are only interested in interrogating one column of information at a time based on the date you locate in the second row.

If this is correct you will need a short table to work on and one cell providing the column number to refer to.

I assume the date to search for is in A1.
In B1 have "=match(A1,sheet2!B2:AF2,0)"

Titles
A3="Indicator" - this is the numbers one to 20 for use in your Large function.
B3="Value" - the Large value you are working with
C3="Position" - where it comes in the designated column
D3-G3 would be formatted "Position +/-" & general (or something) so you can enter the values: -1, -2, 0, 1

In B4 type the following (assuming here a shorter range of rows 3 to 26):
"=LARGE((Sheet2!$A$3:$A$26<>"Account")*OFFSET(Sheet2!$A$3,0,$B$1):OFFSET(Sheet2!$A$26,0,$B$1),A4)"
and enter it as an array function using shift+ctrl+return

In C4 enter "=MATCH(B4,OFFSET(Sheet2!$A$3,0,$B$1):OFFSET(Sheet2!$A$26,0,$B$1),0)"

You now have the values in column C and B1 to give a position of the matched value in your second sheet which you can reference with an Index function, say, using the relative row indicators in D3-G3 within the row value to return the required entry.

I think that should do it. (?)

 

[inkserious]

Ingenious...

I've got it working with one exception: the array formula will not work. It keeps returning a #VALUE error. I did enter it as an array.

Also, one other addition:

I'd like to have any values that are negative returned as largest negative to smallest negative. As the formula stands now, it returns the nth LARGEST value in the column, and of course this is the smallest negative number. Any suggestions?

Thanks again.

 

[DirkStruan]

Check the array function for any titles - this may screw things up. If you play around a bit, you will hopefully be able to get it to work. If not, provide some detail on the data its referring to and we'll try to figure out the problem.

For negatives I suggest you include an IF statement within the LARGE function so if it's negative you multiply everything by -1. (If this will do everything you need?).


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[inkserious]

I'm still unable to get the array formula to work. Don't know why as all the references are correct??

Multiplying the LARGE "k" value by any negative number does not seem to work as it seems the LARGE function will not accept a negative number for the "k" value.

Lastly, how do I handle identical values. For example if there are 2 values of 100, the LARGE function will only return the other cell values for the 1st occurence.

Thanks.

-ep

 

[inkserious]

Still unable to get the Array formula to work.

This is the layout of the worksheet. The formula is entered into a cell on Sheet1. The OFFSET function points to the column number (on Sheet2) defined in B1. THe LARGE "k" value is determined by A10. Once the column is found, the function looks down for the LARGE value.

The problem is that within the column their are numbers that I want to exclude in the LARGE lookup. The cells that I want to exclude have a row label in the range A3:A26 in the form of Account, Account1, Account2, etc.

Thanks again..

 

[DirkStruan]

I don't understand why the array function is not working. Please can you confirm that you are still getting #VALUE and that the function is enclosed by {} to double check it has been entered as an array function (the {} are entered by excel when you enter the function while holding down shift and control).

Given you have added numbers to the account labels you will need a slight amendment to the large function (if you get it to work):
"=LARGE(([highlight]LEFT(Sheet2!$A$3:$A$26,7)<>"Account"[/highlight])*OFFSET(Sheet2!$A$3,0,$B$1):OFFSET(Sheet2!$A$26,0,$B$1),A10)"

I think there was a misunderstanding on the negative values bit.

For ease, set up a cell in which you enter +1 for positive values and -1 for negative. I will refer to this cell as FLAG.

The function then becomes
"=LARGE(FLAG*(LEFT(Sheet2!$A$3:$A$26,7)<>"Account")*OFFSET(Sheet2!$A$3,0,$B$1):OFFSET(Sheet2!$A$26,0,$B$1),A10)*FLAG"



Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[inkserious]

Yes, it is definately being entered as an array formula. I am still getting the #VALUE error. Below find the non-array version that works.

=LARGE(OFFSET(PlayerData!$A$3,0,PlayerData!$B$1):OFFSET(PlayerData!$A$114,0,PlayerData!$B$1),$D17)

However, this does not work:

{=LARGE((PlayerData!A3:A114<>"Account")*OFFSET(PlayerData!$A$3,0,PlayerData!$B$1):OFFSET(PlayerData!$A$114,0,PlayerData!$B$1),$D17)}

I removed the numbers from the end of the Account labels in order to leave out the LEFT function.

I'm losing hair by the minute...

-ep

 

[DirkStruan]

Have you got a mix of strings and numbers in the columns you are performing the Large function on? I'm guessing so - and it will be this that is upsetting the array-large function.

OK, quick rethink to remove the problem:

The easiest way I can see of taking this forward is to isolate the column you want and only show the data you need:

1) You still need to locate the required column using the initial match function.

2) set up a column to pull the information into, but include an IF statement to ignore those where the A column says "account". ie =IF(PlayerData!$A$3<>"account",OFFSET(PlayerData!$A$3,0,PlayerData!$B$1),"----") and copy down for all values.

3) set up a column of integers (for use in the large function)

4) set up a LARGE function based on the range from (2) and the values in (3)

5) enter a match function to find the position of the value in (4) in range (2)

6) set up the position columns as per my earlier post and then use the index function (on the single column in (2) for ease).

D

 

[inkserious]

The PlayerData sheet was being populated via a macro attached to an button on a separate sheet. In order to alleviate all the problems with the Array formula, I decided to trick the LARGE function by forcing the numbers entered into the Account label to be entered as text. I simply added this to my macro before the copy Sub

Sub NumToText()
For Each i In Range("AccountData")
If Not IsEmpty(i.Value) Then
i.Value = i.Text
End If
Next i
End Sub

Thanks for all the help Dirk; I learned a great deal.

-ep

 

[DirkStruan]

No problem - glad you got it sorted.

D