How to categorize using multiple currency rate

[34to35]

Hi, All

I would like to seek assistance. Just want to confirm if there is a way for me to obtain the data without vba coding involved?
The scenario is in column A, there are 2 different teams involve name as "Plato" and "Maxwell"

I want to categorize them based on the price. However case contains multiple condition.

Scenario is any cell with "X" in column D have a threshold of 50,000USD while those with blank have a threshold of 5000USD
However the problem is not all lines have the same currency. And the data does not have any exchange rate reference. Is there a way to do this without referencing any cell or sheet?

Another scenario is if the ticket number in column B is the same, and if these line items contains above 5000 usd then it should be automatically tagged to Maxwell team. Ticket number can contain multiple lines. see column C.

I tried doing simple IF function however the multiple condition gets me the problem.
I hope that you can help me with this.

Thank you!

[Glenn Kennedy]

So how is Excel supposed to know the EUR to USD (or any other) exchange rate????

You could hard code it into a formula, but it would be FIXED.

You need either to refer to a concersion rate table or to an online source.

[34to35]

So how is Excel supposed to know the EUR to USD (or any other) exchange rate????

You could hard code it into a formula, but it would be FIXED.

You need either to refer to a concersion rate table or to an online source.

===========================

Hi Glenn,

You are totally right given that value is dynamic. I have attached a revision of the excel with a sheet for exchange rate.

Thank you

[Glenn Kennedy]

Better!! For the first part... what does your expected result look like??

[34to35]

Better!! For the first part... what does your expected result look like??

Hi Glenn,

The expected result would be the one showing in column A. Either Maxwell or Plato

[Glenn Kennedy]

that's the second part, not the first. In any event... You are expecting a column to conatain a value... and a formula. You can't have that (without VBA). This is as close as you'll get:

Assign Maxwell
=IF(AND(COUNTIFS(C$2:C9,C2)>1,COUNTIFS(C$2:$C$9,C2,$I$2:$I$9,">5000")),"Maxwell",A2)


USD equivalent
=$G2/VLOOKUP($H2,'Exchange rate'!$A:$B,2,FALSE)

Threshold
=IF(I2>IF(E2="X",50000,5000),"Exceeded","OK")

[34to35]

that's the second part, not the first. In any event... You are expecting a column to conatain a value... and a formula. You can't have that (without VBA). This is as close as you'll get:

Assign Maxwell
=IF(AND(COUNTIFS(C$2:C9,C2)>1,COUNTIFS(C$2:$C$9,C2,$I$2:$I$9,">5000")),"Maxwell",A2)


USD equivalent
=$G2/VLOOKUP($H2,'Exchange rate'!$A:$B,2,FALSE)

Threshold
=IF(I2>IF(E2="X",50000,5000),"Exceeded","OK")

Thank you for this. Question though, what for example the unique value is not sorted. meaning items in row 8 is in row 1. Can the count if still work?

[Glenn Kennedy]

What happened when you tried it?

[34to35]

What happened when you tried it?

the return was 0 even though i have extended the range

[Glenn Kennedy]

Diagnosing invisible problems is not easy. Post the sheet.

[34to35]

Diagnosing invisible problems is not easy. Post the sheet.

Hi Glenn,

See attached scenario. The column A is where the result should be in. Formula should not reference in A2.
In this particular scenario, i have added a new line which is 10 and 11. These lines have the same request ticket number (highlighted in blue). For B11, given that the price is above 5KUSD, the end result in A11 and A7 should be Maxwell given that either one of those row contains above than the threshold.

[Glenn Kennedy]

I am now confused, as you seem to have changed the "rules" for column A..

maybe this:

=IF(AND(COUNTIFS(B:B,B2)>1,COUNTIFS(B:$B,B2,$H:$H,">5000")),"Maxwell","Plato")

If not... please re-state the RULES.

[34to35]

I am now confused, as you seem to have changed the "rules" for column A..

maybe this:

=IF(AND(COUNTIFS(B:B,B2)>1,COUNTIFS(B:$B,B2,$H:$H,">5000")),"Maxwell","Plato")

If not... please re-state the RULES.

this now worksss!!! thank you so much Glenn. :D

[Glenn Kennedy]

You're welcome... and thanks for the feedback.

[34to35]

You're welcome... and thanks for the feedback.

Hi, Glenn

Just out of curiosity, is the countif applicable as well if the value in column B contains a letter? The request ticket is just a reference and this can contain Alphanumeric.

[Glenn Kennedy]

Out and away from PC. Try it. It should be 100% OK.

[34to35]

Out and away from PC. Try it. It should be 100% OK.

Hi Glenn!

Hope all is well. I would like to reopen my situation. I hope that you could be able to help me.
The requirement has been changed.

Attached is the revision of the excel formula that you have given to me.

I have added nested IF in the same formula that you given in the past. I would like to confirm if what function should I use to bypass the tagging of "Plato" (see cell B12) if condition is A12 is tagged as "Chris" and C12 contains "Apac".
What I would like to achieve is that if it is Chris with Apac then it should be automatic as tagged as Maxwell.
If it Chris only with no tagging in column C then the formula should run.

I hope that you can help me.

Thank you in advance

[JeteMc]

Try changing the formula in cell B2 as follows:

Please Login or Register  to view this content.

After pasting, drag the fill handle down to cell B13.
Let us know if you have any questions.

[34to35]

Try changing the formula in cell B2 as follows:

Please Login or Register  to view this content.

After pasting, drag the fill handle down to cell B13.
Let us know if you have any questions.

Hi JeteMC,

Thank you so much. This works

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.