Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other cell
Hi,
I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other cell
You could shorten your formula in DZ3 to:
=IF(BA3="","",MAX(DQ3:DY3)) Since your range is consecutive cells, you don't need to list each individually. To get the header, you can use an INDEX/MATCH formula: =IF(BA3="","",INDEX(DQ2:DY2,1,MATCH(DZ3,DQ3:DY3,0) ) HTH Elkar "Struggling in Sheffield" wrote: Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other cell
Here's one way:
1. Insert a row immediately below row 3 2. In Cell DQ4, put the formula '=DQ2' to copy the label below the value 3. In Cell DR3, put the formula '=DR2' to copy the label below the value 4. Repeat for all labels 5. In cell DZ3, put the following formula: =IF(BA3="","",HLOOKUP(DZ3,DQ3:DY4,2,FALSE)) 6. Hide row 4. Hlookup will work, but the value you want must be in the second or subsequent row of the table. You can also shorten your MAX formula to: =IF(BA3="","",MAX(DQ3:DY3)) Cheers! --Dan "Struggling in Sheffield" wrote: Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other cell
Try in E3
=IF(BA3="","",OFFSET(DQ1,0,INDEX(DQ3:DY3,MAX(DQ3:D Y3)))) You can replace =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) with =IF(BA3="","",MAX(DQ3:DY3)) since you are using ALL cells in the range... easier to write and read "Struggling in Sheffield" wrote: Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other
Hi Elkar,
Very many thanks for that, worked a treat. I only turn to the forum after several hours of trying (and failing!) myself. Cheers, Steve. "Elkar" wrote: You could shorten your formula in DZ3 to: =IF(BA3="","",MAX(DQ3:DY3)) Since your range is consecutive cells, you don't need to list each individually. To get the header, you can use an INDEX/MATCH formula: =IF(BA3="","",INDEX(DQ2:DY2,1,MATCH(DZ3,DQ3:DY3,0) ) HTH Elkar "Struggling in Sheffield" wrote: Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other
Hi Dan,
Tried Elkar's idea first and that worked fine so problem sorted, but thanks for trying to help, much appreciated. Cheers, Steve. "Dan" wrote: Here's one way: 1. Insert a row immediately below row 3 2. In Cell DQ4, put the formula '=DQ2' to copy the label below the value 3. In Cell DR3, put the formula '=DR2' to copy the label below the value 4. Repeat for all labels 5. In cell DZ3, put the following formula: =IF(BA3="","",HLOOKUP(DZ3,DQ3:DY4,2,FALSE)) 6. Hide row 4. Hlookup will work, but the value you want must be in the second or subsequent row of the table. You can also shorten your MAX formula to: =IF(BA3="","",MAX(DQ3:DY3)) Cheers! --Dan "Struggling in Sheffield" wrote: Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select max value from 9 cells, copy cell col heading to other
Hi Sheeloo,
Tried Elkar's suggestion first and that worked fine, but thanks very much for trying to help, appreciate it. Cheers, Steve. "Sheeloo" wrote: Try in E3 =IF(BA3="","",OFFSET(DQ1,0,INDEX(DQ3:DY3,MAX(DQ3:D Y3)))) You can replace =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) with =IF(BA3="","",MAX(DQ3:DY3)) since you are using ALL cells in the range... easier to write and read "Struggling in Sheffield" wrote: Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select dependent cells in the result cell | Excel Discussion (Misc queries) | |||
How do I randomly select cells regardless of cell content? | Excel Worksheet Functions | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
How to copy single cell into cell that is merged from two cells? | Excel Discussion (Misc queries) | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) |