Help needed to create a specific Excel formula

[imported_unknown]

Hello everyone

This is my very first time here ... found it through a Search Engine, so hope I am using the write forum to ask this question??

I am trying to create a formula that will do the following, and I am getting myself all tied up with it ... it's driving me nuts

What I want to do is have the formula look for the highest figure among 10 cells.

Rather than return the highest value (ie using MAX) I'd like it to return the text that is in the cell 9 rows immediately above it.

There will be times however that there will be more than one cell with exactly the same highest figure, in which case, to get to the above result, I will need the formula to work out which of the equal highest cells in value is closest numerically (by referring to a number in a cell adjacent to it)to the figure in a cell on another sheet within the workbook.

Gee, this is hard to explain. I hope that all makes sense?

Anyway, I might leave it here and see what response I get as to whether I need to explain something in greater detail or if I am barking up an unclimbable tree so to speak.

Thank you most sincerely for any help or advice and I look forward to hearing from you all.

Thanks again

 

[SueQ]

Hi Aladin ... me again

Thanks, that seemed to do the trick (ie I got text in the last of the cells, eg A19). The only thing is that instead of returning June in this instance it returned Bob. Although Bob also had the highest value (eg 6) along with June, June actually had the figure (53) that was closest to the figure at F13 (51) ... Bob's was 45.

Why do you think that might be?

Aladin, if it is easier for you, I am more than happy for you to email me direct at sueq@bigpond.net.au - but I will leave that for you to decide.

We're getting there though, aren't we

Thanks again for your support and advice Aladin ... look forward to hearing your thoughts.

Bye for now

Sue

 

[Aladin Akyurek]

On 2002-10-10 00:29, SueQ wrote:
Hi Aladin ... me again

Thanks, that seemed to do the trick (ie I got text in the last of the cells, eg A19). The only thing is that instead of returning June in this instance it returned Bob. Although Bob also had the highest value (eg 6) along with June, June actually had the figure (53) that was closest to the figure at F13 (51) ... Bob's was 45.

Why do you think that might be?

Aladin, if it is easier for you, I am more than happy for you to email me direct at sueq@bigpond.net.au - but I will leave that for you to decide.

We're getting there though, aren't we

Thanks again for your support and advice Aladin ... look forward to hearing your thoughts.

Bye for now

Sue

The formula in A18 as setup (see the recap) is only good if C7:S7 is sorted in ascending order. Since that is out of question, we need a different array formula...

(A)

=IF(A16>1,MAX(IF(((IF((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15,(C7:S7))-Sheet2!F13)^2=MIN((IF((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15,(C7:S7))-Sheet2!F13)^2))*IF((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15,(C7:S7)),IF((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15,COLUMN(C7:S7))))-CELL("Col",C7:S7)+1,0)


The above formula needs to compute thr same thing many times. We can avoid repetitive computations using the SETV/GETV pair from the morefunc.xll add-in...

(B)

=IF(A16>1,MAX(IF(((IF(SETV((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15),(C7:S7))-Sheet2!F13)^2=MIN((IF(GETV(),(C7:S7))-Sheet2!F13)^2))*IF(GETV(),(C7:S7)),IF(GETV(),COLUMN(C7:S7))))-CELL("Col",C7:S7)+1,0)

(C) If the morefunc add-in cannot be installed for some reason, we can use Steve Dunn's UDF, called V, instead...

=IF(A16>1,MAX(IF(((IF(V((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15),(C7:S7))-Sheet2!F13)^2=MIN((IF(V(),(C7:S7))-Sheet2!F13)^2))*IF(V(),(C7:S7)),IF(V(),COLUMN(C7:S7))))-CELL("Col",C7:S7)+1,0)

What follows is the code for V...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Aladin

 

[SueQ]

Hi Aladin

The formulas all work beautifully, thank you. I have chosen your option B above and provide below a recap of the final formulas used.

In cell A15: (array entered)

=MAX((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10))
In cell A16: (normally entered)

=SUMPRODUCT((A15=(MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10))+0)

In cell A17: (array entered)

=MATCH(A15,(MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10),0)

In cell A18: (array entered & using the morefunc add-in)

=IF(A16>1,MAX(IF(((IF(SETV((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15),(C7:S7))-Sheet2!F13)^2=MIN((IF(GETV(),(C7:S7))-Sheet2!F13)^2))*IF(GETV(),(C7:S7)),IF(GETV(),COLUMN(C7:S7))))-CELL("Col",C7:S7)+1,0)

In cell A19: (array entered)

=INDEX(IF(MOD(COLUMN(B1:R1)-CELL("Col",B1:R1)+0,4)=0,(B1:R1),""),IF(A18,A18,A17))


Aladin, thanks and thanks again for all your time and effort in assisting me ... I am extremely happy with the results; the spreadsheet is perfect and just what I was looking for.

Ta again


Sue
Adelaide, South Australia
This message was edited by SueQ on 2002-10-12 17:42