Or function or what else?
- Thread starter Ufoo
- Start date
Thanks
rahulshewale1
Active Member
Thanks. Manual always works. But with excel you can handle almost all data, whether large or small. That is why I want to learn it more. I know there is a way. I just want someone to help me connect the dot. And I like adventure. Manual is not adventurous
Peter Bartholomew
Well-Known Member
= IF(priority<TRANSPOSE(priority), currency1¤cy2,"")
shrivallabha
Excel Ninja
What the posting person means here is demonstrate all columns correct results manually so that a formula or other approach could be verified! This would be because the logic that you think is clear may not be clear at all for the other person!!
Peter Bartholomew
Well-Known Member
I think the idea was that the manual result should be sufficient to indicate the desired output of any formula.
BTW My formula is a single array formula, the natural output of MS365 but more cumbersome in other versions of Excel.
Thanks. Let me clarify.
Column H should only have USDMXN as shown in the attached sheet.
Column K should only have GBPZAR and USDZAR.
I have failed to get one dynamic formula for that. That is why there is no formula in columns H and K. Thanks
shrivallabha
Excel Ninja
Please clarify below queries.
1. Does the range N1:O10 have any relation to calculations? If yes, how exactly is it supposed to be used in formulas.
2. You have two different formulas in ranges B2:K11 and B15:K24 respectively. Do you need assistance with either one or both of them?
2. In column H we should have only USDMXN as probably MXN has 0 (n) assigned against it and therefore only one pair (n+1). Is this the logic behind number of pairs to be shown?
3. In column H, why USDMXN is the desired outcome? Why MXNAUD or MXNCAD are not desired outcomes? What rule does govern this calculation?
Having said this, @Peter Bartholomew has already posted his logic. Have you checked it?
1. Does the range N1:O10 have any relation to calculations? If yes, how exactly is it supposed to be used in formulas.
2. You have two different formulas in ranges B2:K11 and B15:K24 respectively. Do you need assistance with either one or both of them?
2. In column H we should have only USDMXN as probably MXN has 0 (n) assigned against it and therefore only one pair (n+1). Is this the logic behind number of pairs to be shown?
3. In column H, why USDMXN is the desired outcome? Why MXNAUD or MXNCAD are not desired outcomes? What rule does govern this calculation?
Having said this, @Peter Bartholomew has already posted his logic. Have you checked it?
Hello,
1. Yes to number 1. Take GBPCAD for example. If I concatenate straight, in range B2:K11 I will have GBPCAD and CADGBP. N1:O10 is used so that the formula returns GBPCAD only, because the number against GBP in N1:O10 is bigger than the number against CAD
2. I need help in table B2:K11 only
3. MXN having 0 is not the logic behind USDMXN. To elaborate that I am uploading an image which is showing what I want the formula to return.
4. For MXN and ZAR, I want the formula to return USDMXN, USDZAR and GBPZAR only.
I forgot to comment on @Peter Bartholomew. I do not understand what priority means. Is it a named range or what? I cannot see that in formulas. @Peter Bartholomew please help. Thanks
Attachments
Peter Bartholomew
Well-Known Member
'priority' was the name I gave to the sequence of values to the right of your image. 'currency1' are the currency codes as a column and 'currency2' the same codes as a row range [the use of defined names is just my personal style; if I know what a data object represents, I name it; if I don't know what it represents, I delete it to avoid confusion!].
It quickly became clear that I wasn't going to return the values you are expecting. If EUR (9) is an empty column then my logic would suggest MXN (0) should be fully populated, [changing the direction of the inequality transposes the array].
It quickly became clear that I wasn't going to return the values you are expecting. If EUR (9) is an empty column then my logic would suggest MXN (0) should be fully populated, [changing the direction of the inequality transposes the array].
Thanks for your help. This formula (=IF(VLOOKUP($B3,$O$2:$P$11,2)>VLOOKUP(C$2,$O$2:$P$11,2),$B3&C$2,"")) has almost given me what I want. The only challenge is MXN and ZAR. The problem is that with MXN I only want USDMXN, and with ZAR I only want USDZAR and GBPZAR.
I have tried ($A2&B$1="USDZAR")+($A2&B$1="USDMXN")+($A2&B$1="GBPZAR")<1 but it has returned cells with USDZAR, GBPZAR and USDMXN only. So I have failed to combine the 2 formulas above. Thanks.
Peter Bartholomew
Well-Known Member
Formulas are only useful if there is a logical pattern to capture. Otherwise, it is more effective to build the array manually. An Excel 365 formula that captures the basic logic as well as individual rules linking Mexico and South Africa to other currencies might be
If you do not use Excel 365, I could build the filters using defined names but I refuse to use direct cell referencing.
Code:
= LET(
priorityFilter, priority>TRANSPOSE(priority),
MXfilter, 1 - (currency2="MXN")*(currency1<>"USD"),
ZAfilter, 1 - (currency2="ZAR")*((currency1<>"USD")*(currency1<>"GBP")),
combinedFilter, priorityFilter*MXfilter*ZAfilter,
IF(combinedFilter, currency1¤cy2,"") )If you do not use Excel 365, I could build the filters using defined names but I refuse to use direct cell referencing.
Thanks guru. But honestly I do not understand your suggestion. It will take me a while. I am very new to excel. After a while I have come up with this formula: =IF(VLOOKUP(B$1,$N$1:$O$10,2)>0,IF(VLOOKUP($A2,$N$1:$O$10,2)>VLOOKUP(B$1,$N$1:$O$10,2),$A2&B$1,""),IF(B$1="zar",IF(OR($A2="GBP",$A2="USD"),$A2&B$1,IF(B$1="mxn",IF($A2="USD",$A2&B$1),"")),""))
It might not be the best but it is working perfectly. You just put it at the first cell and you drag along and across. But for the formula to work you must have the helper table in my original excel sheet. If anyone has a better way please share. Thanks to all senior and junior gurus
Last edited:
Peter Bartholomew
Well-Known Member
See your point. If you do not have the Excel 365 function, you weren't in with a chance. I have moved the formula into defined names which probably will not aid understanding but the workbook should work.
Thank you so much guru. Very informative