Extracting the cell associated with the Max Value from a row

[Waboku]

Hello,

Thanks for considering helping me with this. I have a row of data and it goes price, qty, price, qty, etc... I would like to extract the cell associated with the max value. So if the max value is in the 1st cell, I would like to extract the value in the cell to the right of the the 1st cell. If the max values in the 29th cell I would like to extract the value in the 30th cell.

If someone could make something that fits the sample below I think I could modify it for my data.

A	B	C	D	E	F	G	H
1	12	1	9	2	5	3	1

<tbody>
</tbody>

B1, D1, F1, are prices I would find the max value for.

C1, E1, G1 are qty's.

H1 is the outcome of the function.

Thanks again, please consider.

 

[Gerald Higgins]

Hi, the values in C, E, G are 1, 2, and 3.
Is this significant ?
Will they always be 1, 2, and 3, and if there were more entries the next would be 4 and so on ?
Or could these be different values ?

Also, is it POSSIBLE that a price figure in B, D, F could also appear as a quantity in C, E, or F ?

 

[Eric W]

Your design makes the formula difficult. It would be much easier if you could put the price on top and quantity in the row below. With them on the same line, we have to make some tricky moves to make sure that we don't mix up prices and quantities. Nevertheless, this should work in H1:

=MAX(IF(MOD(COLUMN(B1:G1)-COLUMN(A1),2),IF(B1:G1=MAX(IF(MOD(COLUMN(B1:G1)-COLUMN(A1),2),B1:G1)),A1:F1)))

confirmed with Control+Shift+Enter.

 

[Waboku]

Hello Eric W,

Thank you for the impressive formula. I had to alter it slightly because of an error I made in my example. The 1 below the A should really be blank, it was meant to indicate the row number but it shouldn't be below the A then. So I replaced A1:F1 at the end of the formula with C1:G1 and tested it on some samples and it worked perfectly. Maybe that isn't the correct solution. If you know if I corrected the error and don't mind letting me know I would appreciate it. Thanks again.

Hello Gerald Higgins,

The 1 2 3 values aren't significant, the entries are actually more like random numbers, 1 2 3 was just for the sample. It is possible that a price and quantity can be the same. I appreciate you considering my problem but I don't want to waste your time. I think Eric W's solution is perfect and if I edited it correctly I don't need any further help with this. Thanks.

 

[Gerald Higgins]

OK, thanks for letting us know it's fixed !