Please help with formulas generating values a fixed distance from the large value in a set of data

[Waboku]

Hello,

I am having a problem with something for my work. I would really appreciate someones help if anyone knows how to do the following. Please consider. I would like to use a formula in 3 cells to generate the value a certain distance from the largest value in a set of data. For example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="width: 65"]
<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>[TR]
[TD="width: 65"]Product Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Prices (Columns B to AE)
[/TD]
[TD]Quantities (Columns AF to BI)
[/TD]
[TD]Large Price
[/TD]
[TD]Large Quantity
[/TD]
[TD]Price to the left of the Large Price
[/TD]
[TD]Quantity to the left of the large quantity
[/TD]
[/TR]
[TR]
[TD]Name 1
[/TD]
[TD]A number in each cell
[/TD]
[TD]A number in each cell
[/TD]
[TD]=Large(B2:AE2,1)
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

I would like, formulas for the cells with the ?'s,

The large quantity cell is the value 30 cells to the right of the large price from the prices columns not the large price column.

The price to the left of the large price cell is the value one cell to the left of the large price from the prices columns.

The quantity to the left of the large quantity cell is the value 29 cells to the right of the large price from the prices columns.

Thanks

 

[StephenCrump]

Have a look at the Index and Match functions ...

=MATCH(LARGE(B2:AE2,1),B2:AE2,) will return the position of the largest value in B2:AE2, e.g. if C2 (the 2nd cell in the range) is the largest value, then the MATCH function will return 2.

Then: =INDEX(AF2:BI2,MATCH(LARGE(B2:AE2,1),B2:AE2,)) will find the corresponding (i.e. 2nd) value in the range AF2:BI2.

To go left one cell, simply subtract 1 from the result of the MATCH().

But ...

- Is there any possibility that the maximum value is the first cell, i.e. you can't take the value one to the left?

- Do you need to allow for the possibility of duplicate maximum values?

 

[Waboku]

Hello,

I think I got what I need to work using the match and index functions. I inserted blank columns for when the maximum value is in the first cell. When there are duplicates of the largest value I would like Excel to use the value closer to the left. I think it does that automatically.