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

 

[Legacy 98055]

Hi SueQ
Go ahead and post all of the details. I guarantee that someone will solve this. I cannot using native formulas but if all else fails can use VBA. Bet there is a native solution however.

Tom

 

[Juan Pablo González]

Hi, welcome, and yes, you got the right board !

I think what you want is perfectly achievable, but, words can be difficult to understand sometimes. Look at the bottom of this page, there's an utility "Colo's HTML utility" to display worksheets on the board. Try putting some examples there of what you want/need (You don't need too many rows or formatting, just how the data is set up, and the results that you're expecting)

 

[imported_unknown]

Hi there

Thank you both for such speedy and lovely replies ... I don't feel that I am in the wrong place now, and for that I am most grateful

I did the Colo thing and have tried to paste it into this message a few times and all that pastes in is a lot of gobbledigook (like the technical term there? rather than the image of the spreadsheet as it appears on my PC this end. What might I be doing wrong?

I am sure once you can see the image created it will make it a lot easier for me to explain.

I'll wait to hear from either of you (or anyone else that feels like replying of course) as to what I might be doing (or not) that's preventing the pasting from working.

Ta muchly again and I'll hear back from you when you get a chance.

Bye for now

Sue

 

[Aladin Akyurek]

On 2002-10-06 06:45, SueQ wrote:
Hi there

Thank you both for such speedy and lovely replies ... I don't feel that I am in the wrong place now, and for that I am most grateful

I did the Colo thing and have tried to paste it into this message a few times and all that pastes in is a lot of gobbledigook (like the technical term there? rather than the image of the spreadsheet as it appears on my PC this end. What might I be doing wrong?

I am sure once you can see the image created it will make it a lot easier for me to explain.

I'll wait to hear from either of you (or anyone else that feels like replying of course) as to what I might be doing (or not) that's preventing the pasting from working.

Ta muchly again and I'll hear back from you when you get a chance.

Bye for now

Sue

You can also describe your data like this...

"A1:A10 in Sheet1 houses numeric data with Amounts as label in A1.

"E1:F25 in Sheet2 houses data with Dates and Names as labels in E1:E2."

After having described where the data of interes is located, you can specify what must be computed...

 

[imported_unknown]

Hello everyone

Thank you very much for your replies, they are most sincerely appreciated.

I have taken your advice and submit the below in the hope that it will help you all to have a bit better idea of what I'm after.

This is a condensed example (cell wise, ie only 5 rather than 10):

Sheet 1

1. Cells D10,H10,L10,P10 & T10 contain the figures that I'd be searching for the highest figure from.

2. Cells B1,F1,J1,N1 & R1 contain text.

3. Cells C7,G7,K7,O7 & S7 contain a seperate figure altogether.

Sheet 2

4. On another sheet in the workbook the cell F13 contains a different figure again.

The cells in 1. 2. & 3. relate to each other respectively, ie D10,B1 & C7 form one 'set', H10,F1 & G7 another, and so on.

Now for what I am trying to achieve:

a. I'd like to formula to search cells D10,H10,L10,P10 & T10 for the highest value.

b. Once the cell at has been found (and let's suppose it is H10), I'd like it (the formula) to return the text in the cell that relates to it, ie F1 in this example.

c. If though, more than one of the cells has the same highest value, I then need the formula to refer to those cells from C7,G7,K7,O7 and S7 that are applicable (ie, only those that relate to the highest number cells not the others) to ascertain which cell of these contains the figure closest to the figure in F13 on sheet 2.

As an example, suppose H10 and P10 both contain the highest value. I would want the formula to check cells G7 & O7 to find out which one is closest in value to the value at F13 on sheet 2.

d. Once this is known, the same result is wanted that is at b. above. That is, to return the text from either F1 or N1 on sheet 1.


I'm thinking that this probably hasn't made it any easier after reading it back to myself, but I'm hoping it has.

Looking forward to hearing from you all.

Bye for now

Sue

 

[Aladin Akyurek]

On 2002-10-06 18:43, unknown wrote:
Hello everyone

Thank you very much for your replies, they are most sincerely appreciated.

I have taken your advice and submit the below in the hope that it will help you all to have a bit better idea of what I'm after.

This is a condensed example (cell wise, ie only 5 rather than 10):

Sheet 1

1. Cells D10,H10,L10,P10 & T10 contain the figures that I'd be searching for the highest figure from.

2. Cells B1,F1,J1,N1 & R1 contain text.

3. Cells C7,G7,K7,O7 & S7 contain a seperate figure altogether.

Sheet 2

4. On another sheet in the workbook the cell F13 contains a different figure again.

The cells in 1. 2. & 3. relate to each other respectively, ie D10,B1 & C7 form one 'set', H10,F1 & G7 another, and so on.

Now for what I am trying to achieve:

a. I'd like to formula to search cells D10,H10,L10,P10 & T10 for the highest value.

b. Once the cell at has been found (and let's suppose it is H10), I'd like it (the formula) to return the text in the cell that relates to it, ie F1 in this example.

c. If though, more than one of the cells has the same highest value, I then need the formula to refer to those cells from C7,G7,K7,O7 and S7 that are applicable (ie, only those that relate to the highest number cells not the others) to ascertain which cell of these contains the figure closest to the figure in F13 on sheet 2.

As an example, suppose H10 and P10 both contain the highest value. I would want the formula to check cells G7 & O7 to find out which one is closest in value to the value at F13 on sheet 2.

d. Once this is known, the same result is wanted that is at b. above. That is, to return the text from either F1 or N1 on sheet 1.


I'm thinking that this probably hasn't made it any easier after reading it back to myself, but I'm hoping it has.

Looking forward to hearing from you all.

Bye for now

Sue

In A15 in Sheet1 array-enter:

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

In A16 enter:

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

In A17 array-enter:

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

In A18 array-enter:

=IF(A16>1,MATCH(Sheet2!F13,IF(MAX((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)),(G7:S7))),0)

In A19 array-enter:

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

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin

 

[SueQ]

Hi Aladin

Thanks sincerely for the effort you have put in in devising and typing out the formula(s) ... I am most grateful.

Since writing the initial request I have spoken to a friend of mine who is an Excel guru (like you guys) and he also has come up with an option (albeit heaps longer than yours)!!!

I'm hoping my work week won't be too busy and I will be able to get back to my spreadsheet soon and try out both of the formulas (yours and his) - but it may not be for a few days yet. Thought I'd just let you know.

I promise I will get back to you and let you know how it all turns out, and once again my most sincere thanks. I'm eternally grateful.

Kindest regards

Sue

 

[SueQ]

Hi Aladin

Well, I have finally got around to trying the 2 formulas I have been given, and yours wins hands down for ease of entering and simpleness, thank you 100 times over

There's only one problem though, in the final formula (ie in the cell A19 on your example) the returned result is a blank cell rather than the text I was after. All the other cells return a value of some sort.

Perhaps to make it easier I will let you know the figures and text I used, and you might be able to tell me what I have done wrong?? I'd be most grateful.

In cells D10, H10, L10, P10 & T10 I have the figures 4, 6, 6, 3 & 5 respectively.

In cell F13 the figure is 51.

In cells C7, G7, K7, O7 & S7 I have the figures 49, 45, 53, 43 & 51 respectively.

In cells B1, F1, J1, N1 & R1 I have the text Adam, Bob, June, Lisa & Tom respectively.

According to my reckoning, the text June should have been returned in cell A19 of your example (ie, the highest in value in cells D10:T10 with the closest margin to F13 on sheet 2 (51).

To get a blank result in A19 must mean that I have done something wrong in entering wouldn't you suspect? Although, as I mentioned, there were figures in the other 4 cells as you had described (in this example they were A15 - 0, A16 - 13, A17 - 4 and A18 - 6. But alas A19 had nothing in it.

Anyway, will leave you now to ponder what it is that I may have done incorrectly, and look forward to hearing back from you only at your convenience so that I might be able to keep working on it.

Once again Aladin, thanks and thanks again, ... look forward to hearing from you.

Bye for now

Sue

 

[Aladin Akyurek]

On 2002-10-09 07:36, SueQ wrote:
Hi Aladin

Well, I have finally got around to trying the 2 formulas I have been given, and yours wins hands down for ease of entering and simpleness, thank you 100 times over


There's only one problem though, in the final formula (ie in the cell A19 on your example) the returned result is a blank cell rather than the text I was after. All the other cells return a value of some sort.

Perhaps to make it easier I will let you know the figures and text I used, and you might be able to tell me what I have done wrong?? I'd be most grateful.

In cells D10, H10, L10, P10 & T10 I have the figures 4, 6, 6, 3 & 5 respectively.

In cell F13 the figure is 51.

In cells C7, G7, K7, O7 & S7 I have the figures 49, 45, 53, 43 & 51 respectively.

In cells B1, F1, J1, N1 & R1 I have the text Adam, Bob, June, Lisa & Tom respectively.

According to my reckoning, the text June should have been returned in cell A19 of your example (ie, the highest in value in cells D10:T10 with the closest margin to F13 on sheet 2 (51).

To get a blank result in A19 must mean that I have done something wrong in entering wouldn't you suspect? Although, as I mentioned, there were figures in the other 4 cells as you had described (in this example they were A15 - 0, A16 - 13, A17 - 4 and A18 - 6. But alas A19 had nothing in it.

Anyway, will leave you now to ponder what it is that I may have done incorrectly, and look forward to hearing back from you only at your convenience so that I might be able to keep working on it.

Once again Aladin, thanks and thanks again, ... look forward to hearing from you.

Bye for now

Sue,

Glad to see you tested the system of formulas. Here a recap with small modifications...

In A15 array-enter:

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

In A16 normally enter:

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

In A17 array-enter:

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

[ Added 0 as the third argument to MATCH ]

In A18 array-enter:

=IF(A16>1,MATCH(Sheet2!F13,IF((MOD(COLUMN(D10:T10)-CELL("Col",D10:T10)+0,4)=0)*(D10:T10)=A15,(G7:S7))),0)

[ this formula has undergone a significant change ]

In A19 array-enter:

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

which delivers June as result regarding the sample values you provided.

For formulas to be array-entered, you need to hit control+shift+enter at the same time, not just enter.

If you want, I can send you the workbook showing all this.

Aladin