Mel Smith
Well-known Member
- Joined
- Dec 13, 2005
- Messages
- 1,023
- Office Version
- 365
- Platform
- Windows
I have the following data in these cells and I am trying to put a formula together which will give me the 'best' outcome. This is calculated by the best number in front of the "/" coupled with the lowest number after the "/"
the expected answer in this example is: 2\10. My formula, so far is:=MAX(DO3,DR3,DU3,DX3)&"/"&MIN(DP3,DS3,DV3,DY3) but as you can see, it gives me an incorrect answer. It's giving me the best in the 'front' number and the best in the 'back' number but not with them coupled as they are in the cell (in this case cell DW3.
Any help will be most appreciated.
Mel
the expected answer in this example is: 2\10. My formula, so far is:=MAX(DO3,DR3,DU3,DX3)&"/"&MIN(DP3,DS3,DV3,DY3) but as you can see, it gives me an incorrect answer. It's giving me the best in the 'front' number and the best in the 'back' number but not with them coupled as they are in the cell (in this case cell DW3.
Any help will be most appreciated.
Mel
Book1 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
DN | DO | DP | DQ | DR | DS | DT | DU | DV | DW | DX | DY | |||
1 | ||||||||||||||
2 | Figures | Wickets | Runs | Figures | Wickets | Runs | Figures | Wickets | Runs | Figures | Wickets | Runs | ||
3 | 2\11 | 2 | 11 | 1\9 | 1 | 9 | 2\10 | 2 | 10 | |||||
4 | ||||||||||||||
First Class |
Cell Formulas | ||
---|---|---|
Range | Formula | |
DN3 | DN3 | =IF(AY3="~~~","",AY3) |
DO3 | DO3 | =IF(AY3="","",LEFT(AY3,FIND("\",AY3)-1)+0) |
DP3 | DP3 | =IF(AY3="","",REPLACE(AY3,1,FIND("\",AY3),"")+0) |
DQ3 | DQ3 | =IF(BK3="~~~","",BK3) |
DR3 | DR3 | =IF(BK3="","",LEFT(BK3,FIND("\",BK3)-1)+0) |
DS3 | DS3 | =IF(BK3="","",REPLACE(BK3,1,FIND("\",BK3),"")+0) |
DT3 | DT3 | =IF(BW3="~~~","",BW3) |
DU3 | DU3 | =IF(BW3="","",LEFT(BW3,FIND("\",BW3)-1)+0) |
DV3 | DV3 | =IF(BW3="","",REPLACE(BW3,1,FIND("\",BW3),"")+0) |
DW3 | DW3 | =IF(CI3="","",CI3) |
DX3 | DX3 | =IF(CI3="","",LEFT(CI3,FIND("\",CI3)-1)+0) |
DY3 | DY3 | =IF(CI3="","",REPLACE(CI3,1,FIND("\",CI3),"")+0) |