• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Or function or what else?

Ufoo

Member
Hello excel experts,

The problem in the attached sheet seemed simple but it has taken me a while. Please help. Thanks
 

Attachments

  • Book1.xlsx
    12.1 KB · Views: 13
Hi @Ufoo,

Please provide desire result manually.
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
 
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
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!!
 
Manual is not adventurous
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.
 
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!!
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
 

Attachments

  • Book12.xlsx
    12.7 KB · Views: 6
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?
 
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?
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

  • Desired result.png
    Desired result.png
    62.9 KB · Views: 7
'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].
 
'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].
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.
 
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
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&currency2,"") )

73261

If you do not use Excel 365, I could build the filters using defined names but I refuse to use direct cell referencing.
 
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
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&currency2,"") )

View attachment 73261

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:
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.
73293
 

Attachments

  • Filtered Exchange Rates.xlsx
    16.4 KB · Views: 5
Back
Top